CODEWITHSUNDEEP
google-sheets
Cjmarkham
Cjmarkham

Reputation: 9681

Count every nth block of rows and cells

Given I have the following sheet data

enter image description here

What I'm trying to do is count the amount of times Yes appears in each column.

So, I'm looking for something like

Header 1: 
  B: 1
  C: 1
  D: 0
Header 2: 
  B: 1
  C: 4
  D: 1
Header 3: 
  B: 2
  C: 1
  D: 0

I've tried looking at using MOD and with the formula

=ARRAYFORMULA(MOD((ROW(B1:B14)), 5))

I can get the index, then maybe do some offset? The above formula prints

1
2
3
4
0
1
2
3
4
0
1
2
3
4

I'm not too sure where to go from here though.

Desired output

enter image description here

EDIT

Based on the accepted answer, I amended the formula slightly. This was because the cell in A wasn't guaranteed to be empty if it wasn't a header, so I used a REGEXMATCH

=
ArrayFormula(
  query(
    {
      if(mod(row('Runs 93 - 300'!H:H), 14) = 0, 
        "",
        vlookup(row('Runs 93 - 300'!H:H), {
          if(regexmatch('Runs 93 - 300'!H:H, "RUN #\d+"), row('Runs 93 - 300'!H:H)),
          'Runs 93 - 300'!H:H
        }, 2)
      ),
      if('Runs 93 - 300'!M:Q="Yes",1,)
    },
    "select Col1,count(Col2),count(Col3),count(Col4),count(Col5), count(Col6) where Col1 is not null group by Col1 label count(Col2) '',count(Col3) '',count(Col4) '', count(Col5) '', count(Col6) ''"
  )
)

This does make the first cell in the formula output #RUN 300 #RUN 300 but I can accept that.

Upvotes: 1

Views: 284

Answers (2)

z..
z..

Reputation: 12823

You can try this too:

={filter(A:A,A:A<>""),index(transpose(len(array_constrain(trim(RegexReplace(split(transpose(query(B:D,,9^9)),"  ",0),"No|\s",)),9^9,3))/3))}

enter image description here

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can use Vlookup to copy the header down through the rows that are blank in column 1, then use a Group By query to do the counts:

=ArrayFormula(query({if(mod(row(A:A),5)=0,"",vlookup(row(A:A),{if(A:A<>"",row(A:A)),A:A},2)),if(B:D="Yes",1,)},"select Col1,count(Col2),count(Col3),count(Col4) where Col1 is not null group by Col1 label count(Col2) '',count(Col3) '',count(Col4) ''"))

enter image description here

You could avoid doing a Vlookup on every row (or rather four out of five rows) by limiting it to the rows where the data is:

=ArrayFormula(query({if(row(A:A)>counta(A:A)*5,"",vlookup(row(A:A),{if(A:A<>"",row(A:A)),A:A},2)),if(B:D="Yes",1,)},"select Col1,count(Col2),count(Col3),count(Col4) where Col1 is not null group by Col1 label count(Col2) '',count(Col3) '',count(Col4) ''"))

Upvotes: 3

Related Questions