Count rows which meet three conditions ???

[Aad]

Hi everyone,

On my worksheet I will only count the rows which meet three conditions:

The values in column ‘U’:< 45
The values in column ‘U’:>135
The values in column ‘V’ may not be 0 (<> 0)
Using the COUNTIF function I am able to get the results of each condition separately. (=COUNTIF(U3:U64000; ">135"))
However, combining all the conditions for the same row will require some VBA I guess.

Can someone provide me a way how to do this?

Thanks, Aad.

[Bob Phillips]

How can a value in U be < 45 AND > 135, just not possible.

[Aad]

How can a value in U be < 45 AND > 135, just not possible.

Correct, it can't.
What I mean is that the value in U is <45 OR >135 AND not 0

Aad

[Bob Phillips]

=SUMPRODUCT(--((U2:U200<45)+(U2:U200>135)0,--(V2:V200<>0))

[Aad]

=SUMPRODUCT(--((U2:U200<45)+(U2:U200>135)0,--(V2:V200<>0))


Thanks for your respons,

I've tried your suggestion but Excel found an error in the formula and it comes with the following suggestion which is not the correct result: =SUMPRODUCT(--((U2:U200<45)+(U2:U200>135)*0--(V2:V200<>0)))

This might have something to do with this localized country settings.

[Bob Phillips]

Sorry about my typo, it should be

=SUMPRODUCT(--((U2:U200<45)+(U2:U200>135)),--(V2:V200<>0))

[Aad]

Sorry about my typo, it should be

=SUMPRODUCT(--((U2:U200<45)+(U2:U200>135)),--(V2:V200<>0))

That did the trick.
I was not aware of the SUMPRODUCT function.

Any idea how to implement another condition which limits the result to only unique values in column A.

Thanks

[Bob Phillips]

=SUMPRODUCT(INDEX(((U2:U200<45)+(U2:U200>135))*(V2:V200<>0)*(MATCH($A$2:$A$200&"",$A$2:$A$200&"",0)=ROW($A$2:$A$200)-ROW($A$2)+1),0))