sumproduct returns zero

[Guest]

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

 

[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
------------------------------­------------------------------­----------------

 

[Bruno Campanini]

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.

It should be:

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

Bruno

 

[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!

 

[Guest]

Thank you so much! I have been working on this for months.

 

[Guest]

Thank you very much! It works!

It should be:

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

Bruno

 

[Ken Wright]

You're very welcome

Regards
Ken...........................

 

[Guest]

Thank you, that was fast.

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!