How to add ROWS FX to a CountIFS fX so the last factor updates vertically and horizontally

[yourik]

Hi there,

I have made a sheet that takes raw data from machine maintenance modes and determines the reasons used, the average downtime, amount of times it goes down, etc... Currently, I am trying to automate the sheet as much as possible and am currently stuck with the last factor of each formula.

The sheet has 2 tabs. The "Query" Tab which has the raw data and the "Data" tab has the averages. Currently, I am using three formulas to do this
---=COUNTIFS(Query!$B:$B,C3,Query!$E:$E,C$1,Query!$Q:$Q,$A$2)
---=IFERROR(averages(Query!$L:$L,Query!$B:$B,C3,Query!$E:$E,C$1,Query!$Q:$Q,$A$2))
---=IFERROR(averageifs(Query!$M:$M,Query!$B:$B,C3,Query!$E:$E,C$1,Query!$Q:$Q,$A$2))

As you notice, each formula ends in $A$2. The formulas are copied horizontally and vertically. A2 is reading the month in the "Data" sheet and matching it to the month in the "Query" tab. I would like the formula to somehow state consistent when copied horizontally and update to A9 or A18 as it's copied down and gets to the next month section.

Please let me know how and if this can be done.

[Bo_Ry]

Please try at D3

=COUNTIFS(Query!$B:$B,$C3,Query!$E:$E,LOOKUP("zz",$C$1:C$1),Query!$Q:$Q,LOOKUP("zz",$A$2:$A2))

[yourik]

Thank you BO_RY for the formula. That worked. I seem to be able to add those to the other two formulas without issue.

I really appreciate the help!!