CODEWITHSUNDEEP
excelcountexcel-formulaconditional-statementsfrequency
Ashton Sheets
Ashton Sheets

Reputation: 513

Count number of rows where multiple criteria are met

I'm trying to generate a table that shows a count of how many items are in any given status on any given day. My result table has a set of Dates down column A and column headers are various statuses. A sample of my data table with headers looks like this:

Product | Notice | Assigned | Complete | In Office | In Accounting 
   1    | 5/5/13 |  5/7/13  | 5/9/13   |  5/10/13  |    5/11/13 
   2    | 5/5/13 |  5/6/13  | 5/8/13   |  5/9/13   |    5/10/13
   3    | 5/6/13 |  5/9/13  | 5/10/13  |  5/10/13  |    5/10/13 
   4    | 5/4/13 |  5/5/13  | 5/7/13   |  5/8/13   |    5/9/13 
   5    | 5/7/13 |  5/8/13  | 5/10/13  |  5/11/13  |    5/11/13

If my output table were to contain a set of dates in the first column with the statuses as headers, I need a count of how many rows were at the given status and had not yet transitioned to the next status so that in the Notice column, I'd have a count of rows where the Notice Date was <= X AND where the Assigned, Complete, In Office, In Accounting are all greater than X.

I've used a Sum(if(frequency(if statement to get me REALLY close but I feel like I need to have an AND statement within the second IF like this =SUM(IF(FREQUENCY(IF(AND

Here's what I have that won't work:

=SUM(IF(FREQUENCY(IF(AND(Table1[Assigned]<=A279,Table1[[Complete]:[In Accounting]]<=A279),ROW(Table1[[Complete]:[In Accounting]])),ROW(Table1[[Complete]:[In Accounting]]))>0,1))

If I take the "AND" portion out, this works fine except I need it to ONLY count rows where the given status actually has a date so if an "Assigned" date is empty, I don't want that row to be counted for the Assigned column.

Here's an example of what I'd expect to see in the results. I've listed the count in the each column as well as the corresponding product numbers in parenthesis. The corresponding product numbers are for reference only and won't actually be in the result table.

Date | Notice  | Assigned | Complete
 5/6 | 2 (1,3) | 2 (2,4)  |  0
 5/7 | 2 (3,5) | 2 (1,2)  |  1 (4)
 5/8 | 1 (3)   | 2 (1,5)  |  1 (2)

Upvotes: 2

Views: 2963

Answers (2)

barry houdini
barry houdini

Reputation: 46401

OK, assuming you have the original data in A1:F6 then with 2nd table headers in B9:D9 and row labels in A10:A12 then you can use this "array formula" in B10

=SUM((B$2:B$6<=$A10)*(MMULT((C$2:$F$6>$A10)+(C$2:$F$6=""),TRANSPOSE(COLUMN(C$2:$F$6)^0))=COLUMNS(C$2:$F$6)))

confirmed with CTRL+SHIFT+ENTER and copied down and across (see screenshot below)

As you can see the results are as per your requirement. If you replace dates with blanks it will still work

MMULTis a way to get a single value from each row even when you are looking at multiple columns.

I used cell references because I think that's easier, especially when copying the formula across and having a reducing range.......but you can use structured references if you want

enter image description here

Upvotes: 3

Ross Larson
Ross Larson

Reputation: 2437

Have you tried using COUNTIFS to count based on multiple criteria. It is fairly well documented here: http://office.microsoft.com/en-us/excel-help/countifs-function-HA010047494.aspx (2007+ only)

Basically, you use it like

=COUNTIFS(first_range_to_check, value_you_want_in_first_range, ...)

where the ... represents as many pairs as you want (up to 127 total pairs), note the conditions are AND connection so if you have two pairs, the first pair AND the second pair must return true for that row to count.

Upvotes: 2

Related Questions