Formula or VBA or Combination to count Number of Cells in a merged range! PLEASE HELP!

Hi everyone,

Now before we get cracking, I know how bad Merged Cells are, but trust me, I have no way around it. I have created a visual diary in Excel to track how much time people in my office are spending on projects. Each Cell represents a block of time, let's say 15 Minutes, and then if a member of staff works on a project for 3.5 hours, they select 14 cells in that day's column, merge them, and input the initial of the project, which then gets automatically coloured using conditional formatting. This is the quickest and most efficient input method that ALSO allows me to see the days too and how they break down, rather than just a lump sum of the total time they spend that day...if that all makes sense...

Then at the bottom of each column I was looking to have a cell for each formula which would effectively count how many cells are occupied by each initial INCLUDING COUNTING ALL THE CELLS IN THE MERGED RANGES.

I have attached a spreadsheet as an example which should illustrate my point, but of course, if there are any questions, please do ask me and I will answer!

Also, apologies if I listed in the wrong section, as not sure what the best solution is. Would prefer forumlas if I can have it rather than VBA, but happy to use some VBA to create functions of what have you that then feed into a formula?!

Thanks!




Chris

I'll avoid the 'merged cells are bad' discussion. It usually goes nowhere anyway..

I'd use a helpler column, Say column C for example
C1: =LOOKUP(REPT("z",255),B$1:B1)
Filled down to C20

Then you can use standard countif functions based on column C
=COUNTIF(C1:C20,"LFB")

Hi Ron!

At the moment it seems out of all the responses, yours will probably fit my needs the best (assuming this next request comes off!).

How would I ammend your "CountMatchedCells" formula to look at the first x numbers of the text string in the cells, to see if it matches LFB. Some of the team input the project initials and a brief description of the task carried out (in case they forget!).

Example...in the cell it says "LFB - Answering emails", how can I make the formula just look for the first 3 letters?

Thanks in advance for your help!

Regards,



Chris

Ron,

Genius! Thanks a lot! Works Perfectly!

Thank you to everyone else who posted too, very grateful to you all, people like all of you make this forum what it is!

Ron's just happened to work best for me personally is all.

Thanks again!