Thread: Summing across multiple worksheets

Hello,

I have a workbook I am attaching, I need to sum across multiple worksheets, based on the value in column B being equal to the 7 - 8 AM time. I need to Sum the value in Column F for each day, and report them on the tab named Dashboard. I started doing it a very manual and tedious way and attempted to try indexing but struggled to get it to work.

I am sure there is a simple solution but I am just not seeing it.

Thank you for looking.

Dan

Insert a new sheet, rename it as Begin, and then reposition it so that it is to the left of the Machine2 sheet. Then insert another new sheet, name that one End, and position that between your Machine30 sheet and the Dashboard sheet. You now have a "sandwich" of sheets between Begin and End, with the Dashboard sheet outside this "sandwich". Then, as all your data comes from cell F25 of the machine sheets, you can have this formula in B1 of your Dashboard sheet:

=SUM(Begin:End!F25)

and this will add up all the F25 cells from the sheets between (and including) Begin and End.

Hope this helps.

Pete

Well, you learn something new everyday !!

The advantage of using Begin and End sheets is that you can drag another sheet into that "sandwich" to include it in the totals, or you can drag one outside it to exclude it, so this gives you chance to model different situations if you wish. Also, you can re-position sheets within the range without affecting the totals.

Pete

This is a great tip, Its just improved my bowls scoring sheet to the next level

Glad to be of help - maybe you can click on the "star" icon on any post that helped you.

Pete

Pete,

That is a great solution very simple. Unfortunately it doesn't quite get me what I want, not all the machines started recording data on the same dates, so summing for example F25 does not match up the dates across all sheets. I think for this to work accurately I really need some sort of code that will index off the date and times on each sheet. Thank you though, I wished your solution would work it is very slick.

Dan

Ah yes, I see that Machine 13 has its corresponding data in row 361. What you can do is insert blank rows in the other sheets so that they all present their data on the same row (i.e. 361) (you can hide the blank rows, so the sheets will still look the same as they do now).

Hope this helps.

Pete

Great tip from me as well, for anyone else reading through this thread, just make sure you capitalize the Begin:End.
Couldn't figure out why it wouldn't work as you said it would until I corrected the spelling.
Thanks again.