Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional count of rows dependent on multiple columns
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional count of rows dependent on multiple columns
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! In article . com, "Edwin Castro" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Count Rows with defined values in multiple columns | Excel Worksheet Functions | |||
Sort multiple rows by data in certain columns | Excel Discussion (Misc queries) | |||
How to reverse order of rows and columns | Excel Discussion (Misc queries) | |||
inserting columns within certain rows only | Excel Discussion (Misc queries) | |||
Count Rows with Conditional Format? | Excel Discussion (Misc queries) |