Count empty cells in every other column

[SelfTaughtE]

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!

 

[jasonb75]

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.

=(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.

=(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]

Thanks so much! That worked!