SUMPRODUCT to count results?

[Armitage2k]

Hey everyone.

Currently I am summing up results from a list which is daily getting longer, in order to have a monthly overview.
Generally, Everyday my employees write down how much incentive they have earned and on another sheet I use this formula to see how much incentive which employee has:

=SUMPRODUCT(--(Incentivee!B3:B25=DATE(2009,1,1)),--(Incentivee!C3:C25="FOPM"),--(Incentivee!F3:F25="SZX"),Incentivee!E3:E25<>"")

This means: In the 1-1-2009 employee FOPM has earned a total incentive to location SZX of: $result.

Now, my problem is, I also need to know how many times a day this employee earns an incentive to the location SZX, therefore, I need to count how many entries for SZX on a specific date a specific employee (eg FOPM) has.

I hope this is clear. My personal idea was to use COUNTPRODUCT with the same formula, but I always get the #NAME error... no idea about that...


Hope you can help me,
thanks!
A2k

[ConneXionLost]

COUNTPRODUCT is not a standard Excel function; that's why you're getting the #NAME error.

However, the usage of SUMPRODUCT can either COUNT or SUM.

This is untested, but try:

Please Login or Register  to view this content.

Good Luck!

[DonkeyOte]

To elaborate on CXL's post re: COUNT/SUM

The below

=SUMPRODUCT(--(A1:A2="x"),--(B1:B2>10),B1:B2)

Acts as a multi-conditional SUMIF... it is summing the product of three arrays, where the first array is a Boolean Test - ie A1:A2 = "x" will be TRUE/FALSE (this Boolean is coerced using double unary (--) to the Integer equivalent...of 1/0) the 2nd array is another Boolean test - ie B1:B2 > 10, the last array is simply the contents of B1:B2... so let's assume:

A1: x B1: 10
A2: x B2: 5

We get

=SUMPRODUCT(--{TRUE,TRUE},--{TRUE,FALSE},{10,20})

Which once Booleans are Coerced becomes:

=SUMPRODUCT({1,1},{1,0},{10,20})

So your products are (1 x 1 x 10) + {1 x 0 x 20) .. the SUM of those products = 10

So in the above context we're looking at SUMPRODUCT in a SUMIF type context... ie SUM B where A is x and B > 10

If we wanted to count we could simply dispense with our final array...

=SUMPRODUCT({1,1},{1,0})

The Sum of these Products will simply give us a COUNT of times our conditions have been met, ie (1 x 1) + (1 x 0) = 1

Hopefully from the above you can see where you had gone wrong on your initial attempt ?