Count Cell Numbers

bloubul

Member
I'm in search of a method to count the cell numbers as indicate in the Number
Count tab. The cell numbers are indicate in the Cell Numbers tab.
A formula or macro please. I have used the top 3 ranges in grey to indicate
what I'm looking for. The ranges may be up to 31 sets.

Thank you in advance.

https://www.mediafire.com/file/w4yrtuwm7k83asu/Cell_Numbers.xlsx/file

BlouBul :cool:
 

Frank

Member
Well It took me a while to figure out what you were doing, I think I might eventually have worked it out. It is cell ADRESSES (not cell numbers) you are counting ? You are counting only those which appear in the GREY areas ?

Can I assume you are counting all the cell addresses which all appear on the same ROW in the different groups?

Can I assume that the up to 31 "ranges" you refer to are possible additional COLUMNS to the right in batches of 11 columns labelled B to L ? This will count along up to column OZ which should suffice.
Can I also assume that with the exception of the first range ( which has only 6 rows) that ALL future ranges will have 10 rows ?

On the assumption that I've read your mind correctly then this should work. Note these formulas found some omissions in your manual counting of the addresses.

Note that it also counts the zero appearance of an address but its visibility is suppressed using conditional formatting in my version of Excel.

https://www.mediafire.com/file/8727lcqkr72tvqa/Cell_Adress_counter.xlsx/file

Take a look and see if it does what you wanted.

Frank
 

bloubul

Member
Frank
Thank you Sir, perfect as all ways. Please tell me how to hide all those addresses that has no count, I do not need it. Is there a formula to count how many times, let's say $B$11 did appear in the Address counts.

BlouBul :cool:
 

Frank

Member
Bloubul,

I did say that "Note that it also counts the zero appearance of an address but its visibility is suppressed using conditional formatting in my version of Excel." On my count numbers sheet, Range B2:L11 looks exactly like yours with empty cells where there are zero counts.
I used conditional formatting to hide them. You have not told me what version of Excel you are using. Perhaps there is a compatibility issue with your undisclosed version, if you are not seeing what I'm seeing.

Without using conditional formatting the formulas get fiendishly complex as I have to add a lengthy IF statement around an already complex formula.
Anyway I have done that -changed the formulas to check for zero counts and not display them in the range B2:L11.

It would also have helped if you'd confirmed my assumptions about what to count, in other words ignore everything on the cell numbers sheet OUTSIDE the range B3:L8 and N3:OZ12?

Only if that assumption is correct are the counts correct and there is no need to have any formulas on sheet "number count" below row 12. Confusingly, you have borders around cells below row 12 on sheet "number count" as if you are expecting some counts to appear there! This can never happen if the count range is the grey areas as I assumed.

https://www.mediafire.com/file/y77wdy2qylueq0v/Cell_Adress_counter2.xlsx/file


Is there a formula to count how many times, let's say $B$11 did appear in the Address counts.

BlouBul :cool:

I do not understand this question. It is ALREADY counting $B$11 and all the others in the GREY ranges you secified. Did you mean EVERYWHERE on the entire page ?

Please be more specific and more forthcoming with information if you want help.

Frank
 

Sidebar

Top