0

I am trying to get the number of empty merged cells in a range, say for example the 6-cell range of A1:F1. If the cells weren't merged, I could simply use =COUNTBLANK(A1:F1), but let's say A1:C1 are merged and D1:F1 are also merged. If I have a value in A1:C1, =COUNTBLANK(A1:F1) will output 5 instead of the desired 1.

I'm working in a Google Sheets worksheet that was heavily formatted by the designer to be the output of other data in the spreadsheet. The design uses a lot of merged cells and unfortunately getting rid of them is not an option.

The actual spreadsheet has 60 columns by about 400 rows, and the number of merged ranges in each row varies from 1 (i.e., all 60 columns) to 13 (a header column + 12 data columns), so the method I'm looking for ideally would be able to handle that kind of row-to-row variation (which also limits the practicality of formulas like =COUNTBLANK({A1,D1}) which would work perfectly well in the simple example above).

Any way to accomplish this?

2
  • Please explain why the "row-to-row variation" limits a formula like =COUNTBLANK({A1,D1})
    – Rubén
    May 11 '20 at 1:07
  • Because you would have to manually enter each cell that starts a merged range between {} on a per-row basis. On some rows that'd be just one cell, but on others it would be 12, and doing that over 400 rows would be a lot of manual work.
    – Vincent
    May 11 '20 at 21:58
1

Instead of using A1:F1 use {A1,D1}. The final formula is

=COUNTBLANK({A1,D1})

{A1,D1} is an array having 1 row two columns.

NOTE if your spreadsheet uses a dot as decimal separartor instead of a comma use a slash

6
  • Is there any way to get that to work without individually listing every root cell of a merged cell grouping? It works on the simple example I used in the question with just two merged cell groups, but just listing out those cells gets untenable as the number of merged cells grows.
    – Vincent
    May 10 '20 at 23:47
  • As a further issue, some of the rows have differing numbers of merged cells, so I'd need to edit this each time the merged cells change.
    – Vincent
    May 10 '20 at 23:49
  • Please post a new question including all the relevant details.
    – Rubén
    May 11 '20 at 0:10
  • In lieu of creating a new question, I edited this one. Does that better clarify the limits I'm facing?
    – Vincent
    May 11 '20 at 0:33
  • The edit makes unnecessary this answer. I'll delete it later.
    – Rubén
    May 11 '20 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.