counting cells dependant

[swfarm]

hi guys

Im hoping some of you more intelligent people than me could help me please

Im trying to find out how many times the following occurs

if column x matches cell b2 AND if column y matches cell b13 AND if column z is LESS than Column AA

any help gratefully received

Thanks in advance

[63falcondude]

Try this:

=SUMPRODUCT((X2:X100=B2)*(Y2:Y100=B13)*(Z2:Z100<AA2:AA100))

Changing the ranges to suit your need.

[Phuocam]

Like this:

=SUMPRODUCT((X2:X1000=B2)*(Y2:Y1000=B13)*(Z2:Z1000<AA2:AA1000))

late...

[swfarm]

top notch gentlemen thanks to both of you

[63falcondude]

You're welcome. Glad we could help.

Thanks for the rep!

[swfarm]

gents im really sorry but i missed 2 other criteria out

i also want to know if column e is greater than 0.5 AND if column F is greater than 0.5

[63falcondude]

You can add those criteria in like this:

=SUMPRODUCT((E2:E100>0.5)*(F2:F100>0.5)*(X2:X100=B2)*(Y2:Y100=B13)*(Z2:Z100<AA2:AA100))

[swfarm]

so appreciated. thank you

[63falcondude]

You're welcome!