SUMPRODUCT or COUNTIF?

[David Evans]

Easy One for you today

I want to be able to count the number of amounts in Holdings, where Fee A is less than Fee B.

I have been laboring too long in the trenches today and it's not coming to me ...
Thanks for any assistance

 

[Nebu]

=COUNT(IF($A$2:$A$87<$B$2:$B$87,$C$2:$C$87))
CSE

 

[Hui]

Slightly shorter and doesn't require Array Entering
=SUMPRODUCT((C2:C87>0)*(A2:A87<B2:B87))

I assumed Holding had to be greater than 0

If Holding can be 0 then
=SUMPRODUCT(--(A2:A87<B2:B87))

 

[David Evans]

Thanks guys - I was remiss in the posing of the question - bit vague ...

I want to count the number of Cells that meet All of these criteria
Fee A is less than Fee B
Holding is greater than 0


Yes Hui, Holdings can be zero

Thanks again guys ...

 

[Nebu]

=COUNT(IF($A$2:$A$87<$B$2:$B$87,IF($C$2:$C$87>0,$C$2:$C$87)))
CSE

 

[David Evans]

=COUNT(IF($A$2:$A$87<$B$2:$B$87,IF($C$2:$C$87>0,$C$2:$C$87)))
CSE

Thank you Nebu - appreciate your prompt response!

 

[Nebu]

Hi:

Thanks
Anyways Hui's formula =SUMPRODUCT((C2:C87>0)*(A2:A87<B2:B87)) will also do the trick.

 

[Hui]

I allowed for both situations

 

[David Evans]

I allowed for both situations

Sorry Hui, I misread/understood your original response - Me bad.