Multi-conditional Count

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
Hi and thanks in advance,

I am using Excel 2003.

Looking for a formula that will count rows that meet one condition in a column and two NOT conditions in another column.

So something like:

Count the number of cells in column A that contain "Completed" where the corresponding row in column B does not contain "First" or "Last"


Thanks
 
Someone suggested the following formula which works perfectly ...


=SUMPRODUCT(--($A$2:$A$16="Completed"),--($B$2:$B$16<>"First"),--($B$2:$B$16<>"Last"))
 
Someone suggested the following formula which works perfectly ...


=SUMPRODUCT(--($A$2:$A$16="Completed"),--($B$2:$B$16<>"First"),--($B$2:$B$16<>"Last"))

Actually it is good answer. There other alternative but i think yours is the best. Here is mine: It is an array formula
=sum(((a2:a16)="Completed")*((b2:b16)<>"First")*((b2:b16)<>"Last")) CSE
 
Thanks eisayev,

I try to steer clear of array formulas if possible but yours would do the job also.
 
Why not use COUNTIFS, much more efficient than SUMPRODUCT

=COUNTIFS(A2:A16,"Completed",B2:B16,"<>First",B2:B16,"<>Last")
 
I would use COUNTIFS but I'm still back in the dark ages with Excel 2003 and I think COUNTIFS only appeared in EXCEL 2007.
 
Hi Kev@Radstock,

Nothing against array formulas really except that they are easy to break if someone tabs in and out of the cell and then you have to do the Ctrl + Shift + Enter over again. I just find it simpler to avoid them unless there is no other or better way to do something.
 
Back
Top