sum of values but count duplicated value with certain criteria once

[shj92922]

hello there
i am working with a certain kpi where each person enter his work
sometimes same report number can be worked within group of people e.g 6
so each tested 30 samples with total of 180
so what i need is excel count duplicated report number once
dont count number of samples if it has same report.xlsx

[PaulM100]

Try: =SUMPRODUCT((COUNTIF($B$2:$B$16,$B$2:$B$16)=1)*$D$2:$D$16)

[Glenn Kennedy]

Use:

=SUMPRODUCT(1/COUNTIF($B$2:$B$16,$B$2:$B$16&""),$D$2:$D$16)

[shj92922]

thanks it solved my problem
this was my formula since i am dealing with large number of columns
=SUMPRODUCT(1/COUNTIF(B:B,B:B&""),D:D)

but if somenbody can help me to explain me more about the formula and how it work
i want to study it

thanks

[Glenn Kennedy]

SUMPRODUCT(1/COUNTIF($B$2:$B$16,$B$2:$B$16&""),$D$2:$D$16)

Red included to ignore any blank cells in the range.
Orange: returns the reciprocal of the total number of occurrences of each value. so, if an Order No appears 4 times it returns 0.25 four times.
Cyan those reciprocals are multiplied by the values in D (unique values count as 1 multiple values as 1/n)
Black add 'em all up.

Unless you DO have 1,000,000 rows it is NOT a good idea to use whole column references in SUMPRODUCT. You will experience performance issues.

[shj92922]

thanks
will study it now and i hope i understand it

[Glenn Kennedy]

Formulas/evaluate formula is a really handy tool to follow what Excel is doing. The only problem is that it isn't resizeable, which can be a bit of a pain...