Need to Average numbers based off of 2 Criteria

[nsmjc]

I need to get the Average Counts (G10:G20) based off of the Team (H10:H20) and their Unit (D10:D20)

I can average them individually and I can create a formula by manually entering the cells however the cell data will change

so I need to create an average formula to take the Row average G10 - G20 if cells in the same row have specified criteria in cells H10:H20 and D10:D20

I am not sure if I am making any sense.

I am, thinking to use the name manager to create a dynamic range for Team and Unit which I can do.

What I need to know is how to average based on the two criteria so if H10:H20 has 1AA and D10:D20 has Bravo then then average the numbers in column G. and so forth for 1AA and Alpha 2AA Bravo and 2AA Alpha.

I tried using this formula and variations =AVERAGEIFS(G10:G19,Team=E23,Unit=F22)) with no joy.

I need this formula to appear in the respective cells F23:G24


Attached is my worksheet I am working on
AVERAGE AND.xlsx


Skteer thanks just what I needed I will look at pivot tables also

[sktneer]

Try this....

In F23

Please Login or Register  to view this content.

and then copy across and down.

[sktneer]

If you are referencing the named ranges in the formula, the formula should be like this...

Please Login or Register  to view this content.

and then copy across and down.

Also correct the formula used for your named ranges.

[Richard Buttrey]

Hi,

F23 copied to F23:G24

Please Login or Register  to view this content.

However be advised that averaging averages in the way you are doing is not usually recommended and strictly speaking it's not arithmetically accurate since it ignores the weightings that might occur in the underlying data that is producing the G10:G19 averages. You should always work with the original data. Ifv you do it's likely you'll get a different answer.

[MarvinP]

Hi nsmjc,

I think you can get what you want using a Pivot Table. See the attached.