# Count empty cells in every other column

#### SelfTaughtE

##### New Member
I am trying to count the number of empty cells in a row, but I have merged cells (and no way around not using merged cells). The solution I can think of around having the merged cells count double is to count the blanks for every other column. I have tried COUNTBLANK and writing MOD codes for this with no luck.

Here's an example: Data example.xlsx . On Sheet 2, I am trying to use the formula for each time to correspond with the number of blanks for the same time row on Sheet 1, starting with column C and doing every other column.

Thank you!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### jasonb75

##### Well-known Member
Most people can't download attachments due to security restrictions, this is best guess with no visual example to go on. Ranges can be adjusted as required.

Theory:- Count all blanks, deduct number of non blanks (two cells merged with data will be one blank and one non blank so you deduct one blank for each non blank), divide count of remaining blanks by 2.
Excel Formula:
``=(COUNTBLANK(Sheet1!C2:Z2)-COUNTA(Sheet1!C2:Z2))/2``
I am trying to use the formula for each time to correspond with the number of blanks for the same time row on Sheet 1
For this part you will likely need to use INDEX and MATCH to identify the correct row, assuming times in A2:A10 of sheet 1, with the corresponding time to look up in B2 of sheet2.
Excel Formula:
``=(COUNTBLANK(INDEX(Sheet1!\$C\$2:\$Z\$10,MATCH(\$B2,Sheet1!\$A\$2:\$A\$10,0),0))-COUNTA(INDEX(Sheet1!\$C\$2:\$Z\$10,MATCH(\$B2,Sheet1!\$A\$2:\$A\$10,0),0)))/2``

#### SelfTaughtE

##### New Member
Thanks so much! That worked!

Replies
8
Views
203
Replies
9
Views
294
Replies
6
Views
52
Replies
1
Views
33
Replies
2
Views
117

1,129,930
Messages
5,639,054
Members
417,067
Latest member
rohitbabshet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back