Multi-conditional Count

[deutz]

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

[deutz]

Someone suggested the following formula which works perfectly ...


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

[eisayev]

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

[deutz]

Thanks eisayev,

I try to steer clear of array formulas if possible but yours would do the job also.

[Bob Phillips]

Why not use COUNTIFS, much more efficient than SUMPRODUCT

=COUNTIFS(A2:A16,"Completed",B2:B16,"<>First",B2:B16,"<>Last")

[deutz]

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.

[eisayev]

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.

That is right. Countif (s), iferror, sumifs functions are 2007 and above excel functions

[Kev@Radstock]

What reason do you "Steer clear of Array Formulas"!!!

Thanks eisayev,

I try to steer clear of array formulas if possible but yours would do the job also.

[deutz]

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.