CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Bhavye Mathur
Bhavye Mathur

Reputation: 1077

Repeating a Range in Google Sheets

This question is about making a range repeat in Google Spreadsheets. Here is the formula I'm currently using:

=ARRAYFORMULA(ARRAYFORMULA(SUM(COUNTIFS(SPLIT(REPT("Attendence!B:B;", 2), ";"), {"Name1", "Name2", "Name3"}, Attendence!O:P, "=P"))))

This is an example of what I need:

=ARRAYFORMULA(ARRAYFORMULA(SUM(COUNTIFS({Attendence!B:B, Attendence!B:B, Attendence!B:B, Attendence!B:B}, {"Name1", "Name2", "Name3"}, Attendence!O:R, "=P"))))

Essentially, the Attendence!B:B needs to repeat inside the { } the same number of times as the columns in Attendence!O:R. The thing is, the 2nd Formula would work, except the formula needs to access columns C:JY, which means the number of times I would have to repeat Attendence!B:B an absurd number of times.

So I tried using this: SPLIT(REPT("Attendence!B:B;", 2), ";") and replaced the 2 with the number of columns, but it keeps telling that the length of both the parameters isn't equal.

Simplified Sheet: https://docs.google.com/spreadsheets/d/1PFEz3wz5HOP1cD6HBE-N00yoM1reLwlOpo905cMRW8k/edit?usp=sharing

This question is very similar to this: How to make a range repeat n-times in Google SpreadSheet

However, as much as I tried to implement the solution mentioned in that, It didn't work for me.

Upvotes: 2

Views: 600

Answers (1)

kishkin
kishkin

Reputation: 5325

If you've got "P" for every name and date in columns C:JY, then here is the formula for total:

=ARRAYFORMULA(SUM(--(C:JY = "P")))

And here is the formula to get a column for every name:

=ARRAYFORMULA(MMULT(--(C:JY = "P"), SEQUENCE(COLUMNS(C:JY), 1, 1, 0)))

This is multiplying a matrix by a unit vector (a column of size n x 1) - that gives a column of sums of rows. If you need a row of sums of columns then you need to multiply the same matrix by another unit vector (a row of size 1 x n) like so (changing order of arguments in MMULT is important):

=ARRAYFORMULA(MMULT(SEQUENCE(1, ROWS(C:JY), 1, 0), --(C:JY = "P")))

enter image description here

You might want to fix them a bin in case you have data starting from the second row. Like C:JY -> C2:JY and maybe filtering empty rows out if it .

Alternative solution you can find in this question:

=ARRAYFORMULA(COUNTIFS(IF(COLUMN(C:JY), ROW(C:JY)), ROW(C:JY), C:JY, "P"))

enter image description here

Upvotes: 2

Related Questions