For Every ________, Add "1" to 'this cell'...

Hey all...

I'm currently working on a spreadsheet in regards to some counting I have to do. Please ignore the crudity of this table... Here's what I want Excel to do for me: Every time I have a location of A1xx, I want it to add another "+1" in the Alpha 100 cell. The same applies to each column. I want the "for every" part to ONLY include the first two digits of the location, and ignore the last two digits of the location. If I remove locations, the number(s) decrease(s).

Example: A214, the table adds "+1" to the Alpha 200 cell. If I have another location of A225, now the cell shows 2, location C313, now you have 2 in A200 and 1 in C300, etc.

ALPHA BRAVO CHARLIE DELTA ECHO FOX
100
200
300
400
500
TOTAL


I surely hope I didn't lose anyone, or confuse you. I already have the totals to add up all of the columns above it, and then have a grand total add up all the subtotals. Thanks for the help, all!

Hello & welcome to the forum.

Yes you did lose me there. It was as clear as mud lol.

It's best to attach a sample workbook showing what you have & what you need. You'll likely get plenty of help if you do

Cheers