Excel 2013 complex countif formula

Question

I have a source sheet set up like this:

 Days Open     Month
 10             1
 4              1
 6              1
 2              1
 4              2
 2              2
 -1             2
 4              3
 6              3
 7              4
 3              4

etc

I'm trying to set up a formula to count rows based on the following criteria:

cells in Days Open column <=5 and <>-1 where the month is either 2, 3, or 4 (the worksheet will eventually have month numbers up to 12, and I need to group results quarterly). The total must then be divided by the total of ALL rows in which 2, 3, or 4 appears in the Month column.

I can't seem to get the first part of the COUNTIFS to work with both criteria... this is what I have so far that I'm trying to make work:

=COUNTIFS('Cumulative Complaints'!K:K,"<=5",'Cumulative Complaints'!K:K,"<>-1")/(COUNTIF('Cumulative Complaints'!L:L,"2")+COUNTIF('Cumulative Complaints'!L:L,"3")+COUNTIF('Cumulative Complaints'!L:L,"4"))

I've been looking around here and other excel forums and think maybe SUMPRODUCT is the way to go? I haven't been able to get that to work though, given the criteria needed on the Days Open column (<=5 and <>-1).

Answer 1

Try this SUMPRODUCT() FORMULA:

=SUMPRODUCT(('Cumulative Complaints'!K:K<=5)*('Cumulative Complaints'!K:K<>-1)*('Cumulative Complaints'!L:L>=2)*('Cumulative Complaints'!L:L<=4))/SUMPRODUCT(('Cumulative Complaints'!L:L>=2)*('Cumulative Complaints'!L:L<=4))

When using the SUMPRODUCT the condition AND is replaced with the *. It requires all four conditions to be True to return a 1; 1*1*1*1 = 1 if any are false they return 0 so 1*1*0*1 = 0. So as it iterates through the rows it returns a 1 or a 0 to the be added to the sum.

Answer 2

Wrapping a COUNTIF or COUNTIFS function in a SUM function allows you to use an array of constants as OR citeria.

=SUM(COUNTIFS('Cumulative Complaints'!K:K, "<>"&-1,'Cumulative Complaints'!K:K, "<="&5,'Cumulative Complaints'!L:L, {2,3,4}))/SUM(COUNTIF('Cumulative Complaints'!L:L, {2,3,4}))

This is not an array formula and does not require CSE.

Answer 3

My answer would be to take a different approach.

Excel has a very powerful feature called Pivot Tables, and I think it might be a good fit for your problem and other similar problems you may face.

First, I would add a couple columns to your table, like so:

Days Open   Month   Quarter RecentlyOpened
    10          1       1       FALSE
    4           1       1       TRUE
    6           1       1       FALSE
    2           1       1       TRUE
    4           2       1       TRUE
    2           2       1       TRUE
    -1          2       1       FALSE
    4           3       1       TRUE
    6           3       1       FALSE
    7           4       2       FALSE
    3           4       2       TRUE

• The formula for Quarter is: =CEILING(B2/3,1)
• The formula for RecentlyOpened is: =AND(A2<>-1,A2<=5)

Second, select the table, and do Insert > Pivot Table.

Third, drag from the fields to the boxes, like so:

• Drag Quarter to the ROWS box
• Drag RecentlyOpened to the FILTERS box
• Drag Month to the VALUES box

Fourth, click Sum of Month, and select Value Field Settings to change Sum to Count.

Fifth, set the RecentlyOpened filter to TRUE.

The result is this:

Pivot Tables often provide a solution that is more flexible and easier to read and understand versus complex formulas.