Sum Named Ranges

[tomwins]

The attached file shows a list of expenses by regions and districts. I need to sum different aspects based on multiple criteria. I currently use CONCATENATE and VLOOKUP but that has severe limits due to inflexibility. I'd really like to use named ranges with data in the range changing upon occasion, thus eliminating the need to rewrite the formula every time an assignment or Watchlist changes.

The sort of thing I need to do is determine:

1. Total budgeted amount of Mary's two regions
2. Total Current Month Expenses for Sam's three regions
3. Total of all Expenses on Ted's Watchlist
4. Difference of the Total Current YTD Expenses on Ann's Watchlist and Previous FY YTD

Note: I update the spreadsheet monthly by pasting new data in Columns A:H, so formulas should be able to find, for example, South Region's Budgeted Salary even if it is in a different row from the previous month; the column headers remain the same every month.

While the spreadsheet is a sample, in reality the Assignments change infrequently (less than annually) so Mary's named range list will remain the same for several months. However, the Watchlist (none of which have a named range yet) change every few months. I hope that I can list out these items in a way to identify them as a named range but am not sure how to do this right now.

I've also attached a screen print of the named ranges I've done so far for your reference.

I know there should be a way to do these things but it's more complicated than this Excel user can think-through. Thank you for any suggestions or help you can provide to show me the concepts and processes. Please don't hesitate to offer a different solution rather than named range if it can deal with the changes in assignments/watchlist.

[djapigo]

Hi tomwins,

It's really difficult to get summaries with the way your data is presented... so I've "re-arranged" it to look more like a database, where each column has it's own values... note: I might have interpreted your data incorrectly, so try and arrange it to look more like this...

I calculated the formulas (see bright yellow highlighted cells) to what you need...

If you want something more "input" driven formulas, then we can try and arrange that too... although a bit more complicated...

Anyway, let me know if this works for you... again, rearrange your formulas first, then you can use SUMIFS a lot easier...

Dennis

[Maureen405]

Dennis - I was just about to suggest the same thing. In a datasheet form you can then use pivot tables, subtotals, as well as sum ifs and other functions. The only other suggestion I would make is to add a column for assignments (Mary or Sam) and link it to the appropriate cell with there name. This way if the assignments change you only have to change the name in cell N3 or O3 (In Dennis's worksheet) and all the cells will change accordingly to reflect the new assignment. Good Luck.

[tomwins]

Dennis,
Thank you so much for the reply. I suspected either SUMIFS or SUMPRODUCT could provide a key to this.

In the formula to get the Total budgeted amount of Mary's two regions you refer to N4 and N5:
SUMIFS(Budget,Region,N4,Fund,"TOTEXP")+SUMIFS(Budget,Region,N5,Fund,"TOTEXP")

I had named both N4 and N5 "Mary". In some months, the named range "Mary" may need to include the value of N6 or more. Is there a way to use the Mary named range to avoid duplicating however many of these cells may have data in any given month? I've toyed with it and can't seem to get that to work.

Put another way: if column N is a named range (Mary, in this case), how can it be used in SUMIFS or SUMPRODUCT as a criteria? Can this eliminate the need to repeat the formula for each one of Mary's regions so it captures all the regions listed in the named range "Mary"?

thanks,
tomwins

[tomwins]

... add a column for assignments (Mary or Sam) and link it to the appropriate cell with there name. This way if the assignments change you only have to change the name in cell N3 or O3 (In Dennis's worksheet) and all the cells will change accordingly to reflect the new assignment.

Interesting suggestion!

It is not quite as easy to update but I don't have that many changes, so could work. The more challenging aspect is the Watchlist which changes more often. I was hoping to an easier way to update this.

Thank you both,

tomwins

[FDibbins]

Hi and welcome to the forum

See if this approach will work for you...

1. Add a helper column (I used I), and copy this down...
=IF(RIGHT(A2,1)="0",C2,I1)

2. Then to get the totals you want, use something like this...
=SUMIFS(Budget,$I:$I,$K$4,Description,"Total Expense")+SUMIFS(Budget,$I:$I,$K$4,Description,"Total Expense")

You can modify that for just about all your questions...the key is to ID the regions

[djapigo]

Hi tomwins...

I'm not sure why this works, but maybe you can give it a try...

Instead of =SUMIFS(Budget,Region,N4,Fund,"TOTEXP")+SUMIFS(Budget,Region,N5,Fund,"TOTEXP")

Try this =SUM(Budget*(Region=TRANSPOSE(N4:N6))*(Fund="TOTEXP")) with a couple of changes that you need to do... your ranges cannot include the Header Titles (so no full column ranges, start at A2:A5000 or something)
and secondly, because of the TRANSPOSE function, you need an array-enter (CTRL-SHIFT-ENTER) instead of just ENTER...

So, this might be more of a pain than anything else...

Hope this gives you an idea...

Dennis

[tomwins]

Thank you all for your suggestions. I've pieced together Dennis's suggestions and got it to work.

Not only did I need to use TRANSPOSE, I had to make all the Named Ranges the same length.

I decided to go with SUMPRODUCT formula rather than SUMIFS as it seems easier with the large number of variables I will be working with. I've been picking at this for a couple of months as time allows and I can't believe you've pointed me to the answer. You are amazing!! Now I just need to build the real spreadsheet and make it work. At least, I know I'll have some bright folks to bring any problems to.

But now I can mark this question - "SOLVED".

Thanks,
Tom