How can I add cells that contain a string and an integer?

I work at a library, and I'm supposed to be taking our physical copy of our conference room booking log and making a digital copy of it so it can add up monthly totals for us.

I have a set up that tentatively has time slots with names of patrons merged into multiple hour slots, which is well and good, but I've no idea how to add up the totals.

I would like to be able to have the patron's name and then the number of people in the same cell, but the autosum doesn't recognize the number because there's a string attached to it. I've seen a lot of advice on concatenating, but it seems like that isn't quite what I'm looking for. I've tried formatting the cell under the number category. The closest I've gotten was an error that just says #NAME?

I've got like a tentative grasp of coding and all that, but am actually very new to excel. If anyone could point me to a formula or some formatting that might help me add up the numbers in the same cell without having to separate the number from the string, that would be great. This is what my booking sheet looks like so far:

screeny.png

Obviously, when filled out, there will be more cells with patrons and numbers to add up. Surely there's a way to fix my problem without having to dismantle what I have? :(

What you created is fine for presentation...but, not for calculating. It's working against Excel's strengths...instead of with them.

I'd suggest using two columns for each time slot.
• the first row would center the time across the two columns
• the subsequent rows would use the first column for the description and the second column for the headcount.
something like this:

Please Login or Register  to view this content.

Then you could use simple SUM or SUBTOTAL formulas to aggregate activity

Is that something you can work with?

Glad I could help!