A Question for Excel Whizzkids

0

Can find my way round Excel for the most part, at an intermediate level, but am currently stumped by a dilemma.

I have a spreadsheet that has multiple worksheets on, each for a different financial account - what I would like to know is there a way to find a total value for all the accounts from each different spreadsheet combined, on a set date?

Just to further add confusion though, each account doesn't have an entry for every day - so I am guessing some sort of date range would need to be applied!

Can find my way round Excel for the most part, at an intermediate level, but am currently stumped by a dilemma.

I have a spreadsheet that has multiple worksheets on, each for a different financial account - what I would like to know is there a way to find a total value for all the accounts from each different spreadsheet combined, on a set date?

Just to further add confusion though, each account doesn't have an entry for every day - so I am guessing some sort of date range would need to be applied!

Any help greatfully received.

Just wait 3 months.

“Melancholy is incompatible with bicycling.” James E Starrs
Rarely serious, frequently taken seriously.

Create a blank tab (new / worksheet)
Copy and paste the row and column headers from one of the existing sheets into the new one.
In the first cell where you want a combined value, click in the cell and type =
now click the equivalent number from the first set of data
then add +
then click the next one you want to add.
When you're done just press enter. The total will now be in the cell and you can see the formula up at the top.
To copy this to the rest of the cells and rows, just copy and paste right and down.
This should work unless each tab is arranged differently, but you can still check each individual formula to make sure it is referencing the right cells.

Create a blank tab (new / worksheet)
Copy and paste the row and column headers from one of the existing sheets into the new one.
In the first cell where you want a combined value, click in the cell and type =
now click the equivalent number from the first set of data
then add +
then click the next one you want to add.
When you're done just press enter. The total will now be in the cell and you can see the formula up at the top.
To copy this to the rest of the cells and rows, just copy and paste right and down.
This should work unless each tab is arranged differently, but you can still check each individual formula to make sure it is referencing the right cells.

Thanks for the reply, unfortunately that is what I am already doing, but that is cell/row specific and not, if I am understanding you correctly, date specific!

Where B2:B8 are the values to add up, A2:A8 is the list of dates and E2 and E3 are the start and end dates you want to find. It works, just tried it. Will now try to work out how to sum up multiple values across multiple sheets. I may be some time......

Make sure that the dates are in the same format by the way.

Edit: To keep things simple (because at some point you may want to change things and get utterly confused by some ridiculously convoluted formula in one cell), I would do the above for each sheet and then add up those totals seperately to get your total. Intermediate working sub totals and one total from those essentially.

Where B2:B8 are the values to add up, A2:A8 is the list of dates and E2 and E3 are the start and end dates you want to find. It works, just tried it. Will now try to work out how to sum up multiple values across multiple sheets. I may be some time......

Make sure that the dates are in the same format by the way.

Edit: To keep things simple (because at some point you may want to change things and get utterly confused by some ridiculously convoluted formula in one cell), I would do the above for each sheet and then add up those totals seperately to get your total. Intermediate working sub totals and one total from those essentially.

No Prob. If you dont want intermediate totals then you can use SUM adding each SUMIFS from above for each sheet:

=SUM(SUMIFS(B2:B8,A2:A8,">="&E 2,A2 :A8,"<="&E3)+SUMIFS(B2:B8,A2:A 8,">="&E2,A2 :A8,"<="&E3)+SUMIFS(B2:B8,A2:A 8,">="&E2,A2 :A8,"<="&E3)) etc. Obviously you need to make sure the sheet names and columns are correct for each SUMIFS.