Using an ArrayFormula to expand Countifs both rows and columns

Question

Problem: I am trying to make a dynamic arrayformula countifs that will automatically expand with new rows and columns. The criteria rows and headers are auto populated by unique formulas.

What I have so far:
Headers are from G3:3 (will expand various columns with new data)
Criteria Rows start at A4:A and go down from there (will expand various rows with new data)
Current working formula
={ArrayFormula(COUNTIFS('True Rejections (Do Not Modify)'!$B$3:$B,INDIRECT("$A4:$A" & 'Settings (Do Not Modify)'!$B$1+3),'True Rejections (Do Not Modify)'!$C$3:$C,G$3)); ""; SUM(ArrayFormula(COUNTIFS('True Rejections (Do Not Modify)'!$B$3:$B,INDIRECT("$A4:$A" & 'Settings (Do Not Modify)'!$B$1+3),'True Rejections (Do Not Modify)'!$C$3:$C,G$3)))}
The problem with this formula is I have to copy and paste this into each cell under the header (row 4) to make it count.

I've tried modifying G$3 to G$3:3 but I am getting an error of Array arguments to COUNTIFS are of different size. Image below is a screenshot sorry can't share the live sheet.

The would like to place the formula in the red cell on the YTD Tracker sheet
https://docs.google.com/spreadsheets/d/1Zdm8s-qby7SPRYelLAHmhN46uMROGKhRw8GtfI5nOYg/edit?usp=sharing

I tried using sumproduct like below and getting a similar issue like the countifs =ArrayFormula(SUMPRODUCT(('True Rejections (Do Not Modify)'!$B$3:$B = A4:A12)*('True Rejections (Do Not Modify)'!$C$3:$C = G3:M3))) Error: Array arguments to EQ are of different size — wjwelch1
I had a look at your sheet and cant rly tell what are you after. it looks different from the picture, it has some REF errors and the sample is not rly self-explanatory. can you explain it to me in more detail? — player0
@player0 Ignore columns B-F those work fine on the working spreadsheet. The data on the True Rejections are imported from another spreadsheet (that I don't have access to) and gets updated monthly. With those monthly updates it's possible that new users are added with new issues. My goal is to have a single countifs that can expand both down and right. Hope that helps. — wjwelch1

player0 player0 · Accepted Answer · 2019-12-13T18:04:16

try:

=ARRAYFORMULA(IFNA(VLOOKUP(INDIRECT("A4:A"&COUNTA(A4:A)+2), 
 QUERY(QUERY('True Rejections (Do Not Modify)'!A3:C, 
 "select B,count(B) where B is not null group by B pivot C"), "offset 1", 0), 
 TRANSPOSE(ROW(INDIRECT("A2:A"&
 COUNTUNIQUE('True Rejections (Do Not Modify)'!C3:C)+1))), 0))*1)

without zeros:

=ARRAYFORMULA(IFNA(VLOOKUP(INDIRECT("A4:A"&COUNTA(A4:A)+2), 
 QUERY(QUERY('True Rejections (Do Not Modify)'!A3:C, 
 "select B,count(B) where B is not null group by B pivot C"), "offset 1", 0), 
 TRANSPOSE(ROW(INDIRECT("A2:A"&
 COUNTUNIQUE('True Rejections (Do Not Modify)'!C3:C)+1))), 0)))

Using an ArrayFormula to expand Countifs both rows and columns

1 Answers