sumproduct returns zero

V

vacation

The following formula gives me a zero result :
=SUMPRODUCT((B2:B18="joe")*(F2:F18>"0"))

I want to count all occurences of values>$0.00 in Column F only if column
B=Joe in a filtered list.

Thank you
 
K

Ken Wright

Assuming your values in Col F are real numbers, take the quotes off the 0

=SUMPRODUCT((B2:B18="joe")*(F2:F18>0))

or

=SUMPRODUCT(--(B2:B18="joe"),--(F2:F18>0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
B

Bruno Campanini

vacation said:
The following formula gives me a zero result :
=SUMPRODUCT((B2:B18="joe")*(F2:F18>"0"))

I want to count all occurences of values>$0.00 in Column F only if column
B=Joe in a filtered list.
Click to expand...

It should be:

=SUMPRODUCT((B2:B18="joe")*(F2:F18>0))

Bruno
 
D

Domenic

Remove the quotes for your second condition...

=SUMPRODUCT((B2:B18="Joe")*(F2:F18>0))

For a filtered list, try the following...

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-ROW(B2),0,1))),--(B2:
B18="Joe"),--(F2:F18>0))

Hope this helps!
 
V

vacation

Thank you, that was fast.

Domenic said:
Remove the quotes for your second condition...

=SUMPRODUCT((B2:B18="Joe")*(F2:F18>0))

For a filtered list, try the following...

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-ROW(B2),0,1))),--(B2:
B18="Joe"),--(F2:F18>0))

Hope this helps!
Click to expand...
 
Top