PDA

View Full Version : Solved: Count cells with 3 requirements



Aussiebear
08-14-2008, 04:16 AM
I have a request to count those cells in Column D where the value = "Load Out" and the Value in Column B is => P111 or =< P165.

Apparently in 2007 there is a CountIfs function, but this is not available in 2003, which is where the sheet will be used.

Bob Phillips
08-14-2008, 05:00 AM
=SUMPRODUCT(--(D1:D200="Load Out"),--(B1:B200>"P111"),--(B1:B200>"P165"))

comparing text for greater/less than is fraught with problems. Is P45 > P111 or not? It certainly is a > test.

Aussiebear
08-14-2008, 05:29 AM
So i should strip the P by using trim

Bob Phillips
08-14-2008, 05:42 AM
Trim strips spaces not letters.

You could use MID but this would mean that you cannot use any empty cells. I would use this array formula personally

=SUM(IF(ISNUMBER(A1:A200),(A1:A200<>"")*(D1:D200="Load Out")*(--MID(B1:B200,2,99)>111)*(--MID(B1:B200,2,99)<165)))