Conditional count of rows dependent on multiple columns

[Edwin Castro]

Hi

I'm want to count rows in a worksheet given the following condition:
count row if ((column A has "*approved*") AND (column B,C,D, or E has
"failed"))

In the second condition I'd like to count the row if any of those
columns has "failed".

I'm trying to setup a function (or somethign) where I can then get a
total count given a range of rows.

Any ideas?

 

[Domenic]

If there will only be one occurrence of 'Failed' in any row, try...

=SUMPRODUCT((A1:A10="Approved")*(B1:E10="Failed"))

Otherwise, try...

=SUM((A1:A10="Approved")*(MMULT(--(B1:E10="Failed"),TRANSPOSE(COLUMN(B1:E
10)^0))>0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!