Removing Duplicates from SUMPRODUCT

[highguyuk]

I have a SUMPRODUCT Formula:

=SUMPRODUCT(--(NOT(ISBLANK('[file.xls]D1'!$A$15:$A$2000))),--(MONTH('[file.xls]D1'!$G$15:$G$2000)=1),--('[file.xls]D1'!$K$15:$K$2000=5))

which obviously counts up Column A where Column G equals 1 and Column K equals 5 - and this works fine.

However, Column A is a reference number and there are duplicates of the reference number in Column A, but I only want to count them just once.

Can I do this, and how do I do it please?

[Bob Phillips]

Try this array formula

=SUM(N(FREQUENCY(IF((NOT(ISBLANK('D1'!$A$15:$A$2000)))*('D1'!$D$15:$D$2000=1)*('D1'!$K$15:$K$2000=5),
MATCH('D1'!$A$15:$A$2000&"",'D1'!$A$15:$A$2000&"",0)),MATCH('D1'!$A$15:$A$2000&"",'D1'!$A$15:$A$2000&"",0))>0))

[highguyuk]

File.xls points to an external reference, hence why I had that bit extra in the formula. When I add this back in, I get:

=SUM(N(FREQUENCY(IF((NOT(ISBLANK('[file.xls]District 1'!$A$15:$A$2000)))*('[file.xls]District 1'!$D$15:$D$2000=1)*('[file.xls]District 1'!$K$15:$K$2000=5),MATCH('[file.xls]District 1'!$A$15:$A$2000&"",'[file.xls]District 1'!$A$15:$A$2000&"",0)),MATCH('[file.xls]District 1'!$A$15:$A$2000&"",'[file.xls]District 1'!$A$15:$A$2000 &"",0))>0))

Unfortunately, this is giving me #VALUE errors. But the original formula posted does still work. Hmmmm...

Additionally, will this work when the externally referenced file is closed? - as this is a requirement.

[Bob Phillips]

Did you array enter it, Ctrl_Shift-Enter?

[highguyuk]

No - I didn't realise there was an array enter! Not used this side of Excel before, still learning! For example, I have no idea why I had the MONTH value in my original formula! That now works with the expected value.

However I now have added an additional array of Column G, which has thrown it and now gives me a value of 0.

=SUM(N(FREQUENCY(IF((NOT(ISBLANK('[file.xls]District 1'!$A$15:$A$2000)))*('[file.xls]District 1'!$D$15:$D$2000=1)*('[file.xls]District 1'!$G$15:$G$2000=1)*('[file.xls]District 1'!$K$15:$K$2000=5),
MATCH('[file.xls]District 1'!$A$15:$A$2000&"",'[file.xls]District 1'!$A$15:$A$2000&"",0)),MATCH('[file.xls]District 1'!$A$15:$A$2000&"",'[file.xls]District 1'!$A$15:$A$2000&"",0))>0))

[Bob Phillips]

That would suggest that no rows satisfy all of the criteria.

[highguyuk]

Agreed - but there are rows that satisfy the criteria. I just wanted to make sure the additional criteria in the formula was correct, which I think it is. I'll need to work the next bit out I suppose!

[Marcol]

Not to sure where you are with this, but using a helper column might be easier on memory than array formulae.

The helper formula might also be easier to understand and modify.

See this workbook

[highguyuk]

Still working on this, thanks for your assistance so far.

Marcol, either I’m slightly confused by the attached spread sheet and reading it wrong or I think this goes some way to achieving the opposite of what I’m trying to do. For example in Cell P24, it adds up all those with Code 5 to get the figure 4. Yet the formula I want would add up all the Code 5s within a larger criteria, but still add up to 1.

---------- Post added at 04:17 PM ---------- Previous post was at 03:36 PM ----------

I’m struggling with adding an additional filter to this formula:
=SUM(N(FREQUENCY(IF((NOT(ISBLANK('[File.xls]District 1'!$A$15:$A$2000)))*('[File.xls]District 1'!$D$15:$D$2000=1)*('[File.xls]District 1'!$K$15:$K$2000=5),MATCH('[File.xls]District 1'!$A$15:$A$2000&"",'[File.xls]District 1'!$A$15:$A$2000&"",0)),MATCH('[File.xls]District 1'!$A$15:$A$2000&"",'[File.xls]District 1'!$A$15:$A$2000 &"",0))>0))

The above formula works as it should do and removes duplicates as expected. I now want to add an additional filter on column G, where G will be equal 1. To do this, I’ve tried this:

=SUM(N(FREQUENCY(IF((NOT(ISBLANK('[File.xls]District 1'!$A$15:$A$2000)))*('[File.xls]District 1'!$G$15:$G$2000=1)*('[File.xls]District 1'!$D$15:$D$2000=1)*('[File.xls]District 1'!$K$15:$K$2000=5),MATCH('[File.xls]District 1'!$A$15:$A$2000&"",'[File.xls]District 1'!$A$15:$A$2000&"",0)),MATCH('[File.xls]District 1'!$A$15:$A$2000&"",'[File.xls]District 1'!$A$15:$A$2000 &"",0))>0))

But this additional part doesn’t work. How do I add the additional criteria?

[daddylonglegs]

Your revised formula syntax is fine - that works for me on test data. What's in column G, are they dates, is it a formula? Your original formula used MONTH function against that column

[highguyuk]

Your revised formula syntax is fine - that works for me on test data. What's in column G, are they dates, is it a formula? Your original formula used MONTH function against that column

Thank you for verifying. :-)

I found an error with Column G, which although was Formatted as Number was giving an error as a number as text? Anyway, just needed to convert it to a true number and it's worked. So pleased.

I've added appropriate Stars etc, thanks all again.