Counting against merged and non-merged cells

[Kenneth Rothschild]

I'm having a problem trying to sequentially count... in the following example, you can see that column B is correct to count and show 14 items - and these 14 items are allocated to its own country (we can see there are 7 countries) I merged the items within their respective countries (No, it will never be sorted - but can have future items inserted within each country). Column A shows the merged cells, if any, for each country's items.  

In the attached example

I'm using the "=+B2+1" in B3 cell and so on which is correct...

But, in column A, it is way off with the merged and non-merged cells because the cell references are not pointing to the correct previous cell, be it merged or not.

I hope there is a solution to this...

Thank you!!!

Kenneth

[#2]

@Kenneth Rothschild 

First, for future reference, it's actually a lot more helpful if you upload the actual spreadsheet, rather than just an image.

Second, the solution (or "A solution") that works with the newest versions of Excel--which I hope you have--is this formula in Cell A1. No need to copy it down to cells, merged or otherwise.

=COUNTA(UNIQUE(C2:C15))

That said, you're generally going to be better off NOT merging cells as you've done here. In effect, you're doing manually something that might make it easier to visually differentiate, as a new Country enters the picture, but what happens if/when:

• Italy adds another item
• USA deletes a row
• etc.

If the formula I gave you doesn't work, come back and we'll give you a different way to do it that is less elegant but works..... but please do away with the merged cells first. And upload the spreadsheet itself.