My turn - Can has Excel help pleez?

Hi, Excel gurus. I'm not bad with the program, but I'm kind of stumped on this one. None of the functions I can find seem to work correctly (or at all).

I have data sets of varying (and unknown/changing) length. I am trying to SUM the individual cells in a list of numbers that correspond to specific tags listed in another cell in the same row. Due to the layout of the form I'm making, the data are not in an uninterrupted list, and they can't be sorted.

So I have a list of tags in one column at the left (room names) and a list of data (floor area) in another column a few columns to the right.

What I need is a formula that will calculate and return the combined total floor area of all the rooms with a given name.

Normally I would use one of the lookup functions to return the value for the given name, but A) the data is not properly sorted as required by VLOOKUP, and B) I can't figure out how to get that to sum ALL the numbers that correspond to the target - it just returns the first value that matches, not all of them.

To make matters worse, the data set can vary significantly in length, and I'm trying to make this as user-friendly as possible, so I'd like to avoid functions that require a specified range/array of cells, unless I can use whole column references like B:B.

Thanks in advance - I hope somebody can help.

Here's a clunky way to do it. Immediately to the right of each row, do an if function, sort of like this: =if(a=a, area,0). Fill down as far as the data go. Then sum all the way down that column.

If you use 'I can has' like that then you must include a picture of a cat doing something silly. Sorry, pal... this is the internet and there are rules. Admins, please delete this thread.

How about some sample data?

Based on your description have you looked at sumif?

SumIf( range, criteria, sum_range )

Sumif (A:A,Room B123, D:D)

Will search all the cells in A for Room 123 and return the sum of whatever is in cell D.

Crap... I thought I was gonna be able to help. I know about Hyundai Excels.....

JB, thanks for bailing me out, man!

Bobzilla wrote: Crap... I thought I was gonna be able to help. I know about Hyundai Excels.....

moxnix wrote: Based on your description have you looked at sumif? SumIf( range, criteria, sum_range ) Sumif (A:A,Room B123, D:D) Will search all the cells in A for Room 123 and return the sum of whatever is in cell D.

Well, I did think of that, but either I didn't get it right, or something, because it gave me either no response or error returns. You're right, what you posted is exactly what I'm looking for, but it wasn't working for me when I tried before. I'll give it another shot. Thanks!

Well, you can bring a knife to a gun fight if you want, but you're not gonna win.

Instead, put all the data with no spaces between them in one table (tab) named something. Open up MS Access, Tables, right click, Link, point to spreadsheet, etc., Then Query, New, and Sum your square footage, Group By Room. The SQL would look like:

Select room, sum(sqrftg) as footage from spreadsheettable group by room

I haz nothing to add to this discussion.

Well, I apparently got my SUMIF issue corrected, because now it works as intended. Thanks again, moxnix.

Hess, I appreciate the advice, but I also do not need to bring a field piece to a simple plinking match, either...

Access is more like a 22 Hornet. The field piece would be SQL Server.