1. ## Multi-conditional Count

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

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"))

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

4. Thanks eisayev,

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

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

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

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.

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

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.

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•