SUMIF by ignoring Duplicate Entries in a different column

Hi Guys,

I need a help again. Here is my table:

Code, Qtr, Country, Count
209 575 164 Q1 India 105
209 575 164 Q1 China 105
209 575 164 Q1 India 105
NZNQK8TWBQ7 Q1 India 22
204 561 345 Q1 India 147
204 561 345 Q1 India 147
204 561 345 Q1 India 147
FHN5CQ2PCK4 Q2 India 22
FHN5CQ2PCK4 Q2 India 22
FHN5CQ2PCK4 Q2 India 22
FHN5CQ2PCK4 Q2 USA 22
FHN5CQ2PCK4 Q2 India 22
202 450 605 Q2 India 57
202 450 605 Q2 India 57
202 450 605 Q2 India 57


I need to sum the 'count' and it should ignore the values when the 'code' is repeated more than once.

I have attached my screen-shot with the output what I needed. The red font is a manual entrytable2.jpg

Thanks for your time and support.

Regards,
Vijay N

if your data is in Column A to D, use..

=SUMPRODUCT($D$2:$D$16,IF($A$2:$A$16="",0,1/COUNTIF($A$2:$A$16,$A$2:$A$16)))

Confirm with Ctrl+Shift+Enter and not just Enter

Originally Posted by Ace_XL

if your data is in Column A to D, use..

=SUMPRODUCT($D$2:$D$16,IF($A$2:$A$16="",0,1/COUNTIF($A$2:$A$16,$A$2:$A$16)))

Confirm with Ctrl+Shift+Enter and not just Enter

Thank you Ace_XL. It gives me the out put as 353. However, it should consider the manual entries which are in red font for calculation. If I want the sum for Q1 and for the country india, the calculation should sum all counts for which the code is unique and country is India for Q1.

Also, FYI, my range names are same as headings.

Thanks for your time and please help.

---------- Post added at 02:02 AM ---------- Previous post was at 01:53 AM ----------

Ace_XL,

Your formula is perfect. However, can you add to consider the condition of adding only if the country is 'India' and the Quarter is 'Q1'

That is what I exactly need.

Thanks for your time.

Vijay N

Since you have duplicates across columns, you might be better off creating a separate helper column to identfy duplicates based on Code, country and Quarter (In col E say)

Then use
=SUMPRODUCT($D$2:$D$16*(B2:B16=F1)*(C2:C16=G1),1/COUNTIF($E$2:$E$16,E2:E16))
where F1 is your quarter number and G1 is Country

See attached

I am planning to apply this logic with the file which has about 40,000 row entries.
Will there be any performance impact?

Unfortunately, I wouldn't bet against a performance impact with a SUMPRODUCT across 40000 rows

Also, if there is any other way without helper?

Can't think of another way (without the helper), since your duplicates are not based on just one column.

Give it a shot, and lemme know how it pans out