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)))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.