CODEWITHSUNDEEP
excel
J Q
J Q

Reputation: 13

Excel AverageIf to get total average of multiple rows from a table

I have provided a simple example of what I would like to do. I tried to use AverageIf to get the average of all Monday sales for March and April but the formula only provides me the average of March's Monday sales and excludes April.

Note: Column A is Weekday number, Column B is March Sales, Column C is April Sales.

This is the formula I used:

=AVERAGEIF(A2:A200, "=" & 1, B2:C200), which only provides me with average Monday sales in column B.

How can I adjust the formula to also include April in the total average (i.e. take into account column C rather than just column B alone)

Upvotes: 1

Views: 1115

Answers (1)

John Bustos
John Bustos

Reputation: 19544

Rather than using AverageIf(), try using Average(If()) as an array formula for this kind of scenario.

In your scenario, you could use the following formula to get the average:

=AVERAGE(IF(A2:A200=1,B2:C200))

ENTERED AS AN ARRAY FORMULA using cntrl + shift + enter

Hope that helps.

Upvotes: 1

Related Questions