Probably the easiest thing for many of you excel experts but I cannot figure out how to count a set of cells that were merged and blank as 1 value

So if I have 2 cells merged with nothing in the cell, and I want to count the number of blank cells, this should count for 1 value and not 2.

Anyone know the right formula for this? Count blank is counting it as 2.

What I really wanted was a total count of cells regardless of whether it has a blank or not with merged cells counting as 1

Posting as :
works at
You are currently posting as works at

Repeat with me: NEVER merge cells on a calculation tab. Never! Repeat 3 times to be sure.

Now, a simple, non-VBA way to do it is:

=COUNTA(merged_cell_reference, adjacent_cell)

Explanation: if cell A1 is merged with cell B1, and you do =COUNTA(A1,B1), as long as the merged cell is non-blank, the output will be 1 if the merged cell is non-blank; and if the cells are not merged, and both are non-blank, the output will be 2.

P.s.:you can change to =COUNTBLANK() if you need the blank cells

You need to manually enter the reference since excel merged the cells


Yes! I saw this message late and this is how I ended up doing it:

There was no text within some of the merged cells as they are currently representing spaces within a location. The merged cells represent double spaces, the blank cells, whether merged or unmerged represent sold spaces.

I was able to get a total number after I wrote in the word “sold” in the blank cells then used the CountA formula.

PS, after much research I learned what a nightmare merging cells is soooo this’ll probably be the last project I work on where I do so

Thank you for your help!

Use a formula similar to this one. Use that function and insert the extra part at then end and I subtracted the extra cells that are counted in the merged cells.

Post Photo

Why don’t you just change it from merging 2 cells to align text across selection?


There is no text within some of the merged cells as they are currently representing spaces within a location. The merged cells represent double spaces, the blank cells, whether merged or unmerged represent sold spaces.

I was able to get a total number after I wrote in the word “sold” in the blank cells then used the CountA formula.

You could use the row formula to count the # of rows between 2 points?


Thank you everyone for your input!

There was no text within some of the merged cells as they are currently representing spaces within a location. The merged cells represent double spaces, the blank cells, whether merged or unmerged represent sold spaces.

I was able to get a total number after I wrote in the word “sold” in the blank cells then used the CountA formula.

More Posts

is it a good idea to quit within 3 weeks of joining ? I am getting another offer with a higher title. What could be the long term repurcussions ?


Work place bullying is happening to me. How do I deal with it. Advice?

Which is more lucrative in terms of compensation? Working for mutual insurer or a publicly traded insurer?


Hey ladies, I asked this in the main bowls, but haven't gotten much for responses (one mentioned sec 1202 which was helpful). Is anyone here willing and able to answer the following? TIA.

What do you look for when examining the accounting for a small business stock purchase? Asking for specific types of errors and red flags.


How do you deal with success gracefully?
I come from a working class background and over the past few years my career has taken off and I’ve connected with some big players.
Because I’m not close with my family I tell my friends all my little wins but recently I’ve noticed a shift in certain relationships and I think it’s because of this. I’m not bragging I’m genuinely just proud of myself and don’t have family members to share it with. How do you deal with success gracefully?


Hello everyone! I’m currently studying Business Law at uni (final months). My dreams job is to be an AML officer, but I don’t know how to get into this. I was thinking about getting a CAMS certificate, what else do you think I should do to get hired? Thank you in advance 🙏

If there is anyone here from Amazon's Ad Success team, can you please let me know about the kind of compensation that Amazon offers to Advertising Campaign Specialists with 4 yoe?

Also, would love to know about the work culture at Amazon. Is there any flexibility offered in their return to office policy?

Thanks in advance!


Is 123w's Toronto office just filled with former John St. people? There seems to be a ton of people moving over there to join Mo.


AWS Hello fishes, got a offer for L6 senior solutions architect at aws.

whats the max base i can ask, i feel getting low balled at 170K base. TC 250k.


Desperately trying not to wake the partner in the next room but this game is insane!!!!!


I've been getting horrible back pains, presumably caused by sitting at a desk for long periods. Any recommendations on chiropractors that also offer in-house massage services?


As a leader in cyber, one of my aspirations is to increase the diversity of any organization that I am a part of.

For example…When I was in Big4, I referred several diverse candidates who are thriving & now, at McKinsey & Company, I set a personal goal to refer 1 diverse candidate, especially in (but not necessarily exclusive to) cyber/tech/IT, for every month of my tenure.

Continued in comments…

Post Photo

The diversity officer at my old company:

Post Photo

Thoughts on Indochino for a wedding suit? There is also a Bonobos near me but they look more expensive.


Good evening all,
With my partner we are considering making a move to the countryside of Switzerland, with a gorgeous RE project offering incredible views on immaculate mountains and lake. However, it is far away, and will require 1h+ train/car commute vs. 10 min biking right now. We are scared it will bite us back at some point, although COVID helped w/ remote working being a new norm. The project will be done 2024, so I hope to have a more relax job by then. Any similar experience to share?


Anyone know anything about CPE opportunities? How do they expect anyone to keep their certification active?


Can i reimburse 499 vodafone postpaid fully??,

Is it me or does it seem like every guy has a “dirty Twitter” account these days? Either to post their nudes or semi nude pics or to follow porn stars or other guys showing off their goods… I feel like I’m the oddball here by not having one.


How in the world do you manage to get campaigns on talk shows and news?
Is it all paid for?
Is there a particular person you email or call?
I'm really trying to figure all this out without a PR department at my agency


Additional Posts in Excel Genius

How to xlookup or vlookup partial nonexact values. For example I have 2774739_ABC_9999 and I want to look for 9999.


I’m going through some datasets to match up entity names. Basically I’m just trying to see if one entity name is listed in another spreadsheet. I’ve tried vlookups and matches but the entity names differ a little bit sometimes in the one sheet so they won’t match (extra commas, some words shortened, added inc/llp at the end, etc). Is there anyway to get around this and avoid manually going thru to match?


Is there any formula where I can change the last comma to be "and"?

Post Photo

Hi there! I'm working on the data modeling assignment and looking for some tips for data mapping (how power query or vba can help). Thanks!


Alright geniuses, I am stumped.
I'm trying to calculate growth in rental income and want to compare only for properties that existed across the whole time frame. Since 12mths ago, new properties have been added to the portfolio and so rent is higher, but %growth should not include new properties that have come on.
To illustrate, % growth from month1 to month6 should only include the properties in dark green in the numerator and not the rest of the properties showing income in month 6.

Post Photo

What task would you expect excel of being able to do but you have never been able to make excel do it.


Dynamic Range Question- I have monthly P&L data. From this data, I want to create a PL report that uses the start date and end date input from user and calculates the P&L for that period.


How can I flag cells where the decimal places are XX.XX or higher? I want to find all cells which end in .94 or higher (.95, .96, etc). Would this be conditional formatting or a formula? TIA!


Is there a function key to add an IFERROR statement quickly on a formula the same way you can add an absolute constraint with F4??


Is there a way to use formulas within icon set-based conditional formatting?

I'm trying to compare D2 vs. C2, E2 vs. D2, etc..

I know I could create a helper table w values


Can anyone recommend free or paid excel resources that are more general in nature? I need to up my skills but not in one specific area. Thanks!


I have an excel assessment for a job interview (senior revenue accountant). Any ideas of what skills I should brush up on or learn?


Hello Everyone,
I would like to create a schedule for all of my clients. Let’s say I have platinum, gold clients etc. What would be the best way for me to schedule a call for them based on their profile of gold or platinum? For example, I would start scheduling a call every month for my top clients, then a call every quarter for other categories… I need to track the data, and make sure employees are calling them… something that would alert my employees to call such clients. Thanks!


Is Copy, Paste Link the same as =“randomCell”
I noticed when I pasted cell and came back to it, it would show ref errors where as the =“randomCell” would be okay


Excel noob here but with a CS background.

Why do y'all use vlookup, index, match, sumif, etc over Sum(Filter()) or some other map reduce functions?


Hi folks, trying to use the COUNTIFS() formula, where one of the criteria is to only count unique values within a range. Any suggestions on how to do that?


Any book recommendations on excel modeling for real world business applications?


I have a field in excel that represents the GSID, that I want to concatenate to create a link. Example field GSID is 123-west and the link needs to be for some reason using concatenate with left & right formulas to switch - to _ results in the field throwing an error. Anyone knows of a way to make this work?


What kind of jobs out there are heavy on excel but isn't finance/accounting related?


I'm stumped!
Coworker creates "Sheet A" with invoice numbers
I use Monarch to extract invoice #'s and amounts from another report.
I do a pivot table to compile amounts by invoice. I copy those results to "Sheet Z".
I then do an @vlookup on Sheet A referencing Sheet Z (just the invoice # and amount).
It isn't working unless I go to Sheet Z and type over the invoice # (same number)! All invoice #'s are absolute and in the same font & alignment. Workbook has 10 yrs data. Has worked in past!


New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here


Embed this post

Copy and paste embed code on your site


Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone


Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or