Thread: How to SUMPRODUCT with non-repeated serial number*item

Well, assuming a Serial Number has a 1:1 relationship with "Fruit" (ie serial number 1 would only ever be Apple and never both Apple & Orange) then:

=SUMPRODUCT((B3:B12="Apple")/COUNTIF(A3:A12,A3:A12&""))

The use of &"" is to account for possibility of blank cells in the range specified.

The function as a whole is used to generate an array of COUNTIF results - one for each cell referenced in the A3:A12&""

If in doubt work through the example using the Evaluate tool

=SUMPRODUCT((B3:B12="Apple")/COUNTIF(A3:A12,A3:A12&""))

we know that the first part will generate an Array of:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

ie one Boolean for each cell in range B3:B12 where TRUE means Bn = Apple

We know from above that our COUNTIF will generate an array of COUNTIF results, one for each criteria (A3:A12&"") specified thus we end up with a 2nd array of:

{3;3;3;2;2;1;1;1;1;1}

We know that the act of division will coerce our Booleans from TRUE/FALSE to 1/0 respectively thus we end up with the following values:

{0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;1;1;0;0;0}

(ie TRUE/3; TRUE/3; TRUE/3; TRUE/2; TRUE/2; TRUE/1; TRUE/1; FALSE/1; FALSE/1; FALSE/1)

The SUM of those values is then the result of the unique terms - ie 4.

This ONLY works because the serial number : fruit relationship is 1:1 ... if a serial number could apply to multiple fruits then you would need a more sophisticated approach than the above.

Originally Posted by Lewis Koh

I'm still confused

I can't really detail the example any more fully than I did previously I'm afraid.

It's a pretty basic concept I think ?
In essence you're apportioning 1 based on the frequency with which a given value appears in the dataset.
This means that when the apportionments are aggregated the sum is 1 thereby mimicking a unique count.

Originally Posted by Lewis Koh

where do I find the Evaluate tool in excel?

I missed the fact you are using XL2000 - I don't believe the Evaluate tool was introduced until 2002/3.

Given the above I'm not sure if you can use the F9 route in XL2000 either ?ie highlight a section of the formula you're interested in within the Formula Bar and press F9

Originally Posted by Lewis Koh

I encounter another problem when I try to combine this solution with another solution from another thread. Do I continue posting from here or start a new thread?

Perhaps best to start a new thread - reference the other threads if you deem them significantly important to the new question.

Again it depends on the relationship between NIL and Serial.

If (as implied by sample) the relationship is 1:1 - all items of serial n share the same status in C (be it NIL or other) then:

=SUMPRODUCT((($B$3:$B$12="Apple")*($C$3:$C$12<>"NIL"))/COUNTIF($A$3:$A$12,$A$3:$A$12&""))

If that is not the case - ie items of serial n may be both NIL & non-Nil simultaneously then:

=SUMPRODUCT((($B$3:$B$12="Apple")*($C$3:$C$12<>"NIL"))*(MATCH($A$3:$A$12,$A$3:$A$12,0)=(ROW($A$3:$A$12)-ROW($A$3)+1)))

though note the above is still based on 1:1 relationship between serial:fruit (as before)