Count empty cells in every other column

SelfTaughtE

New Member
Joined
Mar 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top