- 24,344
- Midlantic Area
- GTP_Duke
Hi, Excel gurus. I'm not bad with this shiny device, but I'm kind of stumped on a problem. None of the functions I can find seem to work correctly (or at all) for what I'm trying to do.
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.
I've got several clunky ways around the problem, but all require either readjusting the sheet after the data is in, or setting an infinite column of hidden formulae to compile the data. Neither method is particularly friendly to the "set and forget" product I'd really like to have.
Thanks in advance - I hope somebody can help.
[EDIT]
I should add that the ultimate goal is to be able to pluck subtotal out of a varying-length form, to add up for a grand total. However, because of the intended use, I really want to avoid pivot tables and having to array the data in a particular format just so Excel can see it.
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.
I've got several clunky ways around the problem, but all require either readjusting the sheet after the data is in, or setting an infinite column of hidden formulae to compile the data. Neither method is particularly friendly to the "set and forget" product I'd really like to have.
Thanks in advance - I hope somebody can help.
[EDIT]
I should add that the ultimate goal is to be able to pluck subtotal out of a varying-length form, to add up for a grand total. However, because of the intended use, I really want to avoid pivot tables and having to array the data in a particular format just so Excel can see it.
Last edited:
