Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns)

p1992 0
Viewed 70
Date Group A Group B Group C Group A
Sub Group A1 B1 C1 A2
1/1/2022 35 12 54 10
1/2/2022 43 45 62 93
1/3/2022 76 65 39 48
1/4/2022 12 25 81 18
1/5/2022 89 76 20 26
1/6/2022 23 87 47 17
1/7/2022 56 59 21 53
1/8/2022 29 51 9 68
1/9/2022 76 8 52 35
1/10/2022 36 53 38 53

User Input
Start Dt - 1/1/2022

End Dt - 1/5/2022

Group - Group B

Question What is the daily average of a Group given the above user input?

Formula =AVERAGEIFS(INDEX($B$2:$E$11,,MATCH($I$3,$B$1:$E$1,0)), $A$2:$A$11, ">="&$G$3, $A$2:$A$11, "<="&$H$3)

Answer
44.6

User will select a start date, end date and Group. I want to compute the daily average of that. The issue arises when there are multiple columns with same group as Averageif takes the first column only.

Issue - How can I find the daily average of Group A for the given dates, given that Group A are in two columns (they can't be combined as there are multiple sub groups)

excel-formula excel
3 Answers

If you are on Microsoft-365 then can try-

=AVERAGE(FILTER(FILTER(B3:E12,B1:E1=I2),(A3:A12>=H1)*(A3:A12<=H2)))

enter image description here

Harun24hr0

If you have an older version you could use:

=AVERAGE(
         INDEX(A1:E12, 
         AGGREGATE(15,6,ROW(A3:A12)/(A3:A12>=G3)/(A3:A12<=H3),   
                   ROW(A1:INDEX(A:A,SUMPRODUCT((A3:A12>=G3)*(A3:A12<=H3))))), 
         AGGREGATE(15,6,COLUMN(B1:E1)/(B1:E1=I3), 
                   TRANSPOSE(ROW(A1:INDEX(A:A,SUMPRODUCT(N(B1:E1=I3))))))))

Entered with ctrl+shift+enter (I think)

In the picture below J3 shows the answer cell. K3 is used to demonstrate the array that is used for the AVERAGE function (a Office 365 spill range).

enter image description here

P.b0

Another old school approach. User input fields in the formula are referenced using named ranges.

=SUMPRODUCT(($A$3:$A$12>=StartDt)*($A$3:$A$12<=EndDt)
*($B1:$E$1=Group)*($B$3:$E$12))/(COUNTIFS($A$3:$A$12,">="&StartDt,$A$3:$A$12,"<="&EndDt)
*(COUNTIF($B$1:$E$1,Group)))

enter image description here

bugdrown0
Related