COUNT matching results of SUMIFS

Question

I need to figure out how many people in my raw data is within a certain average handling time range. There is no issue finding out the average handling time for the specific criteria, but when I add the factor of asking "how many unique individuals are there?" I'm totally blocked..

I do not want to use helper tables, as I would need about 30 tables, and since they are dynamic both vertically and horizontally, 30 worksheets.

Example of raw data:

Date	Person	Total handling time in seconds	Total interactions
2021-07-11	Person A	22453	46
2021-07-11	Person B	22249	36
2021-07-12	Person A	17970	34
2021-07-12	Person B	5367	9

So now, if I take this:

=(SUMIFS(table1[Total handling time],table1[Date],"2021-07-11")/86400)
/
(SUMIFS(table1[Total interactions],table1[Date],">2021-07-11")

Then I get 0.00630966 which formats as 9 minutes and 5 seconds.

I now know that that is less than 10 minutes, so I can wrap an IF condition around it and get to know that this person is within the time frame. But how do I instead get the result to be "1" instead, as in "One person met this criteria"?

I would like to achieve this without helper columns etc. and if possible do it in one formula. I also cannot use a sumproduct formula, as the date criteria is a spilled range, and in my experience, a sumproduct formula will not spill using a reference like "A1#" for example.

EDIT: Removed image of table as table is now fixed. Also, for clarification I want to do something like this (but it is not possible, as the AND criteria here is a criteria, and not a range:

=COUNTIFS(
          Person,"*",
          Date,"2021-07-11",
          AND(
              ((SUMIFS(Total handling time in seconds,Date,"2021-07-11")/86400)/SUMIFS(Total interactions,Date,$I$18))>0.00462962962962963,
              ((SUMIFS(Total handling time in seconds,Date,"2021-07-11")/86400)/SUMIFS(Total interactions,Date,$I$18))<0.00543981481481481
          ),TRUE
)

user_16431029 · Answer

Depending on your version of Excel you could use the filter function for this.

I replicated your table above in A1:D5, then used:

J2 as a cutoff for the date
J3 as upper limit for seconds/interaction
J4 as lower limit for second/interaction

I took these out of the code for easier adjustments, and to make it easy to see current settings. Using seconds was mostly about me being lazy. (Note that if you set the filter so that no rows fulfill the criteria, the function will give an error code unless it's given an if-empty input. )

With this you can use

=FILTER(A2:D5,(A2:A5>$J$2)*((C2:C5/D2:D5)<$J$3)*((C2:C5/D2:D5)>$J$4))

to get a filtered version of the table. If you need different or more filters, just adjust in the form of (true/false for array)*(true/false for array)*[...]. In what I did here C2:C5/D2:D5 creates a new array with the results of the calculation. If unsure, check the results with Evaluate Formula.

Put the filtered range into index to choose the second column, and wrap all of that into the unique function. Now you can simply take the rows of the array you get, if you want the number of persons with one or more dates that fulfill the conditions (which is what I think you're asking for). It would look like this:

=ROWS(UNIQUE(INDEX(
FILTER(A2:D5,(A2:A5>$J$2)*((C2:C5/D2:D5)<$J$3)*((C2:C5/D2:D5)>$J$4))
,,2)))

If you wanted to know which ones it is you can simply let the unique array spill and you'll see the values.

COUNT matching results of SUMIFS

1 Answers