Array formula for countifs to propagate down the rows (only for rows with data in column A)

0
votes

I am new to google app sheet formulas.

In the image attached i have highlighted the formula, it works. Count if is fetching correct values for B4 cell, that is the count of "y" in Row number 4 where Row number 3 has values Personal

The formula i am using is =COUNTIFS(D4:S4,"y",D3:S3,"Personal")

Now i want to propagate this using ARRAYFORMULA for rows 5 and 6.

I tried with the below formula

=ArrayFormula(if(len(A2:A),COUNTIFS(D4:S,"y",D3:S3,"Personal"))

enter image description here

--Update

After adding the formula provided by Matt i am getting count mismatch for one row enter image description here

--Update #2

As suggested by Eric, i found a space in the Header row enter image description here

1
google-sheetsformulaarray-formulas

1 Answers

1
votes

Unfortunately, it's not quite that straightforward to populate a COUNTIFS that reads sideways like that in the down direction. The common way to do this is with a function called MMULT() which does "matrix multiplication" but is very good for adding or counting across a 2d range like that. In your case because it's dates going sideways, i'm going to assume that the sheet keeps growing to the right? All the way across indefinitely?

You might try this in cell B4:

=ARRAYFORMULA(IF(A4:A="",,MMULT(N(OFFSET(D4,,,9^9,9^9)="Y"),TRANSPOSE(N(D3:3="Personal")))))

You can check out this MMULT() demo sheet I made a while back.