How to pass a filtered range to other functions in Excel?

user_19038770 0
Viewed 63

Let's say I wanted to find the average number of calls per day. Normally, I can use the formula =COUNT(A2:A15)/SUM(1/(COUNTIF(A2:A15,A2:A15))) for the table below, but if I wanted to find the average for a specific person such as George, how would I go about passing the filtered range in the above formula?

I have tried using =FILTER(A2:A15,B2:B15="George") but that gives an error because from my understanding, =COUNT() takes in a range, not an array, which =FILTER() returns.

Call Date Rep Name
2022-01-02 George
2022-01-02 George
2022-01-02 Katie
2022-01-04 Craig
2022-01-04 George
2022-01-05 George
2022-01-05 Craig
2022-01-05 George
2022-01-05 George
2022-01-09 Katie
2022-01-14 George
2022-01-14 George
2022-01-15 Katie
2022-01-15 George
excel-formula excel
2 Answers

You can use a LET-Formula to rebuild the single steps:

=LET(dataUnique,UNIQUE(tblData[Call Date]),
countPerDate,COUNTIFS(tblData[Call Date],dataUnique,tblData[Rep Name],RepName),
AVERAGE(FILTER(countPerDate,countPerDate<>0)))

enter image description here

I am using a table called tblData. Furthermore the formula references F3 named RepName.

user_165784240

You can try:

enter image description here

Formula in E1:

=AVERAGE(BYROW(UNIQUE(FILTER(A2:A15,B2:B15=D1)),LAMBDA(a,COUNTIFS(A2:A15,a,B2:B15,D1))))

Just note that from an analytical point of view it may feel strange to leave out the 9th when George made zero calls. If you want to include this in your average, change the above to:

=AVERAGE(BYROW(UNIQUE(A2:A15),LAMBDA(a,COUNTIFS(A2:A15,a,B2:B15,D1))))
user_97581940
Related