Help with SumIF/SumProduct formulas

[jeremy08]

Hi

This is my first time posting here. I was hoping to get some help with constructing a sumif or sumproduct formula that could help me with consolidating multiple sheets within a workbook. For example, i will have a YTD sheet in the beginning of the workbook and a trailing sheets for each day of the month. I have multiple locations (identical for each sheet) on every sheet. Each location will have its own column. The very first column on each sheet will have a list of revenue and expense accounts. I wanted to try and use a sumif or sumproduct formula to help sum all amounts on my YTD leading sheet for any given account. I was hoping to build a range in the formula so that i could add/delete sheets within the workbook and not have the formula affected. I was only successfull by adding multiple sumif formulas together (=sumif(A3:A5,"supplies expense",Jan!B3:B5)+sumif(A3:A5,"supplies expense",Feb!B3:B5) etc. I was hoping to be able to use the name of the revenue/expense account in the formula in case my different monthly sheets did not contain the same amount of rows. In the above example, i could not make the formula do a range from sheet Jan - Dec for example but instead had to add them up one at a time. Also, is there a way to possibly add a horizontal lookup to this formula so that it could look at every sheet in my range and only at the name of a particular location (column)? I have uploaded a simple example of the layout of what i was trying to use the formula on. Sorry for such a long post.
Thanks.
Jeremy

[quekbc]

Would something like this work for you?

Just make an additional sheet, and move it between Start and End and it will be included in YTD.

[NBVC]

If all the sheets will have the same row headers and columns placed in identical positions, then a simple SUM formula will work.

First add a blank sheet between YTD and Jan and call it Start, add another blank sheet to the end and call it END. These are used as "bookends" so deleting sheets won't mess you up and the formula becomes dynamic.

in B3:

=SUM(Start:End!B3)

copied down and across

[jeremy08]

Thank you both. We are using something like that now, just without the start and end sheets. I also like the drop down options you added as well. We are basically consolidating different monthly financial statements that sometimes look extremely different month over month between accounts that are added so i was trying to explore options where we could sum it by account and location without formating a newly added sheet (month) to the workbook to line up the cells exactly. For example, if i had 5 new accounts and 1 new location in the current month, i wanted to see if i could just add that to the YTD sheet and my formula would automatically look at each sheet and sum it up for me without having to add the new location and accounts to all other sheets in the workbook. Our company has this on a large scale and while our reporting software is being configured, we are using excel in the interim. Any other options out there that i am not thinking about?
Thanks.

[NBVC]

Have a look at this link for a method to do that:

http://www.xl-central.com/sum-single...le-sheets.html

[jeremy08]

Thank you NBVC. I will try and use that.

Jeremy

[jeremy08]

Referencing NBVC's post above, the formula works great. I wanted to see if anyone could help expand on it. Example of the formula unrelated to my prior attachment:

=sumproduct(sumif(indirect("'"&A2:A4&"'!G2:G5"),C2,indirect("'"&A2:A4&"'!H2:H5")))

I use the A2:A4 as my spreadsheet range

The G2:G5 as my constant from the leading sheet (in this case, its my revenue/expense account number, so that will always be my first column in every sheet within the workbook)

The H2:H5 is the part that i have to change. It is looking at my columns for each sheet so all of my trailing sheets have to be consistent in their column order. Can i get this formula to look for a particular cell and bring back the results of that column? In case my columns are not in the same order on each sheet within the workbook. If its to hard to visualize from my description alone, i could try and through together a quick sample attachment.

Thanks

[NBVC]

I think I understand your request, and I am not sure that is easily possible with this method. Might I suggest you make a column in each sheet that is consistent and just use a formula like =H2 copied down, adapting to the proper column for each sheet... then use that in the main YTD formula?

[jeremy08]

yeah that's kind of how i have it now. sometimes our data comes in all scattered and the columns represent our locations and i thought i could somehow look for a location name which represented a particular column in each sheet that may not actually be in the same alphabetical order. Its not that big a deal for me to make sure that my columns are in the correct order as i add sheets to the workbook as i have already tried it today and have saved a lot of time with this new trick. Guess i was getting greedy and looking for more. Thanks for all of the help and i know i will be back for more soon. Whatever i thought i knew about excel is so minimal compared to the questions and answers i am seeing on this forum.

Jeremy