# Thread: Multi-conditional Count

1. ## Multi-conditional Count

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  Reply With Quote

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

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

3. Originally Posted by 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"))
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  Reply With Quote

4. Thanks eisayev,

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

5. Why not use COUNTIFS, much more efficient than SUMPRODUCT

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

6. 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.  Reply With Quote

7. Originally Posted by 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.
That is right. Countif (s), iferror, sumifs functions are 2007 and above excel functions   Reply With Quote

8. What reason do you "Steer clear of Array Formulas"!!! Originally Posted by deutz Thanks eisayev,

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