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