Converting Sum Formula To Not Count Duplicates In Array

[psmi123]

In the below formula I want to convert the Sum formula to not count for duplicates. I will explain this further.

=SUM(($BB$7:$BV$384)*(--(A7:A384=A16)*(B7:B384=B16)))

With the above formula I am summing the total dollar amounts in a range based on 2 criteria (2 other columns). I tried switching it to a SumProduct formula but its not letting me fit the array into more than one row.


=SUM(($BB$7:$BV$384)*(--(A7:A384=A16)*(B7:B384=B16)))

In ($BB$7:$BV$384) I am referencing the entire array which it can pull data from. I am trying to add up the dollar amounts in within the range without counting the duplicate dollar amounts.



=SUM(($BB$7:$BV$384)*(--(A7:A384=A16)*(B7:B384=B16)))

In (--(A7:A384=A16)I am referencing the first criteria which it must search for



=SUM(($BB$7:$BV$384)*(--(A7:A384=A16)*(B7:B384=B16)))

In (B7:B384=B16)I am referencing the second column of criteria


Is there any way to transform this sum formula to not count for duplicates?

Thanks in advance for the help!

-PS

 

[Rijnsent]

Hi psmi123,

I based my answer on this post: https://www.mrexcel.com/forum/excel...-column-excluding-duplicates.html#post4000841
-> you'd need to add two extra conditions. I made a small sample and tested, the data is in D1:F8, column D is the column you want to sum, in E and F are the conditions:

=SUM(IF(FREQUENCY(IF(ISNUMBER(D1:D8),MATCH(D1:D8,D1:D8,0)),ROW(D1:D8)-ROW(D1)+1),D1:D8))
And adding two conditions:
=SUM(IF(FREQUENCY(IF(ISNUMBER(D1:D8),IF(E1:E8="A",IF(F1:F8="D",MATCH(D1:D8,D1:D8,0)))),ROW(D1:D8)-ROW(D1)+1),D1:D8))

Cheers,

Koen