1

I'm trying to count how many cells are included in specific groups of cells with specific first and last character as counting criteria.

With separate counts per group.

Here's what the end result should look like:

https://imgur.com/wnFbFz9

So that for each group of cells beginning and ending with the character "#" as first and last character, the result should sum the cells/rows from the 1st cell to the last cell of that group.

So that for each group of cells beginning and ending with the character "^" as first and last character, the result should sum the cells/rows from the 1st cell to the last cell of that group.

But the formula should exclude from count any group of cells not beginning and ending with the characters "^" or "#" as first and last character.

And ideally, display the sum result on the last cell of each group to the left (as in the B column on the above screenshot).

Up to now, I've only come up with the =ROWS() function to manually count the cells of those groups. But it is tedious.

For example, to deal with the screenshot example I would do:

=ROWS(C3:C5)   (with 3 as result in cell B5)

Then

=ROWS(C8:C10)  (with 3 as result in cell B10)

Nothing in cell B15

Then

=ROWS(C23:C25)  (with 3 as result in cell B25)

Etc.

How to get these results with a single formula over the whole B column?

Lod
  • 657
  • 1
  • 9
  • 30

1 Answers1

0

In the meantime I've found this post:

Sum vertically until empty cell on Google Sheets

Which answers partially the count part of my post.

It got me thinking and I figured these 3 steps.

1st step: count all the cells with len.

=IF(len(C2),1,"")

enter image description here

2nd Step: Adapt the formula:

IF(C2="",SUM(C3:INDEX(C3:C,MATCH(TRUE,(C3:C=""),0))),"")

Found on the post above, to this (taking into account more than one intermediary blank row (D1, D2, D3, D4, etc):

=IFS(OR(AND(D1="",D2="",D3="",D4=""),AND(D1="",D2="",D3=""),AND(D1="",D2=""),D1=""),SUM(D2:INDEX(D2:D,MATCH(TRUE,(D2:D=""),0))),TRUE,"")

enter image description here

Step 3: Moving down the step 2 formula into cell F4 and dragging down from there to have the results at the last cell of each groups (instead of on the cell above each group as with the original formula location on E2)

enter image description here

Remaining questions:

How to specify the sum function to operate on the specific characters ("#" and "^" on the example) basis instead of on the len Function of cell criteria?

How to change the zero cells results to blanks?

How to combine the 2/3 steps formulas into a single step formula?

Here's a copy of the spreadsheet if need be:

4:How to count groups of cells with specific first and last characters wrapping those groups in Google Sheets?

Alternatively, following this post's answer by @shawnrad:

Count rows with not empty value

I used the LEFT and RIGHT functions here:

=IF((OR(LEFT(C1,1)="#",RIGHT(C1,1)="#",LEFT(C1,1)="^",RIGHT(C1,1)="^")),1,"")

to come to these results:

enter image description here

=IF(OR(COUNTIF(C2,"#*"),COUNTIF(C2,"*#"),COUNTIF(C2,"^*"),COUNTIF(C2,"*^")),1,"")

enter image description here

How to then apply the SUM function or any other function to sum the cells/rows per intervals between (and including) the returned results of the last function?

Lod
  • 657
  • 1
  • 9
  • 30