Maintaining Summary references

[amartino44]

Hello, I've tried asking this question before but I don't believe I explained it well enough.

I have a tab with a TON of line items that are updated each month (the months are in the columns and the line items are in the rows). Every so often, the order of the rows changes. This is not a problem because I just use a lookup to reorganize the data. However, I have a section called "summary" at the top, which grabs from the rows below to come up with totals. When the row order changes, the formulas (which are just simple row 10+12+14, for example) in these summary lines are no longer valid. Is there a way that I can use some type of lookup rather than the simple add formulas so that when the order changes in the future those summary totals will still grab the correct rows regardless of the order? Thanks!!!

[MrShorty]

Maybe try =SUMIF()?

[amartino44]

Hmmm, could you expound on that? I'm not very advanced with excel as you can tell. The line items would be something like 0000111 340000 Food Expense. How would I create a sumif based on these types of line items? Thank you.

[MrShorty]

Here's MS's attempt to explain the sumif() function http://office.microsoft.com/en-us/ex...854.aspx?CTT=1 I'm thinking that something like =sumif(lookuprange,lookupvalue,sumrange) where you put appropriate references in for each argument would create the sum you want no matter what order they end up in.

[amartino44]

Another follow up question. Would you have to do sumif+sumif+sumif etc. if you have multiple items? In the column there are 300+ line items and I'm summing, in some cases, as much as 10 distinct items. So, it would be =sumif (columnA,sales,column "x") but then I would need to do that for all the items that I need to sum. Would it be possible to sum a named range? Like, name 5 items in column A, "Sales"? Thanks.

[amartino44]

Oh, I like that. Thank you.

[MrShorty]

I'm sure there are several ways to do it.

You might look at the SUMIFS() function to see if that is what you want.

I like to use helper cells/columns to help organize my thinking. One approach I might take is to add a helper column using OR() and/or AND() to determine which entries will be part of a sum. Then you can use SUMIF(helper column,true,sumcolumn) to get the total you need. If you use the unary operator (--and() for example), the helper column will contain 1's and 0's, and you are on your way to learning how to use =SUMPRODUCT() for more complex conditional sums like many solutions that are proposed around here.

[amartino44]

That went right over my head. Could you try rewording it? Do you mean you could put a classification column, with group 1, group 2, etc? This might help make the sumif/ifs easier to deal with, but it still doesn't help if all of the rows change (I don't think) Also, I didn't understand the use of AND/OR in your example. Sorry

[oeldere]

@Amartino44

do youself a favoure and add an excel file, without confidentional information.

please also add the desired result.

then other forum member can better help you.