sum with multiple sheets

  • Thread starter Thread starter Fawn
  • Start date Start date
F

Fawn

I submitted this before but cannot see it on the list. Sorry if this is
duplicating



Is it possible to use the formula Sum(start:end!b1) with a condition.

What I want to do is sum anything between the sheets start and end that
match a criteria.

I have 12 different sheets which recaps the sales revenue per sales person.
Each and every one of the sheet the salepeople are not in the same order.
So what I am trying to do is sum the sheets if the criteria matches the
master sheet.

Please let me know if this is possible. Right now I have them sum using
name ranges.

Thanks for your help

Fawn
 
Hi Fawn
I had a bit of a play and found that this works up to 12
sheets.
However, greater minds than mine will probably give you a
much simpler answer..

=SUM(SUMIF(Sheet1!Range,"=Criteria")+SUMIF(Sheet2!
Range,"=Criteria")+SUMIF(Sheet3!Range,"=Criteria")+SUMIF
(Sheet4!Range,"=Criteria"))

Regards
Michael
 
The most straightforward way to do it would be to have a SUMIF cell on each
sheet (the same cell) and then add the SUMIF cells across the sheets.
Unfortunately, Excel doesn't support most 3-D functions.
 
Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula on sheet X
=SUM((THREED('sheet1:sheet3'!A1:A1000)="Na")*(THREED('sheet1:sheet3'!B1
B1000)))
Click to expand...

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains our criteria. You can
replace the hardcode "Na" with a cell reference on sheet x. Something
like the following formula in B1 on sheetx:
=SUM((THREED('sheet1:sheet3'!$A$1:$A$1000)=A1)*(THREED('sheet1:sheet3'!
$B$1:$B$1000)))
and copy down
 
Back
Top