Sumif values from different sheets
 

I have a workbook with several sheets where I need to have the sum from all
column d when the criteria in columns c matches a criteria in d12 on my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

Hi
how are your sheets named. That is do they follow a numbering rule,
etc.

--
Regards
Frank Kabel
Frankfurt, Germany

"Hagge" schrieb im Newsbeitrag
...
I have a workbook with several sheets where I need to have the sum
from all
column d when the criteria in columns c matches a criteria in d12 on
my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the
formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

One way.
Put the sumif on each sheet with an indirect reference to d12 of the master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.

One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select alltype the
formula in the cell desiredafter the error msgdelete from the master and
use the sum in para 1.

--
Don Guillett
SalesAid Software

"Hagge" wrote in message
...
I have a workbook with several sheets where I need to have the sum from
all
column d when the criteria in columns c matches a criteria in d12 on my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

No, they have random names

"Frank Kabel" skrev:

Hi
how are your sheets named. That is do they follow a numbering rule,
etc.

--
Regards
Frank Kabel
Frankfurt, Germany

"Hagge" schrieb im Newsbeitrag
...
I have a workbook with several sheets where I need to have the sum
from all
column d when the criteria in columns c matches a criteria in d12 on
my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the
formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

Hi
then you need to create a list of these worksheet names on your
mastersheet. Lets assume you have these sheet names in the range
X1:X10. Now use
=SUMPRODUCT(SUMIF(INDIRECT("'" & X1:X10 & "'!C1:C100"),D12,INDIRECT("'"
& X1:X10 & "'!D1:D100")))

--
Regards
Frank Kabel
Frankfurt, Germany

"Hagge" schrieb im Newsbeitrag
...
No, they have random names

"Frank Kabel" skrev:

Hi
how are your sheets named. That is do they follow a numbering rule,
etc.

--
Regards
Frank Kabel
Frankfurt, Germany

"Hagge" schrieb im Newsbeitrag
...
I have a workbook with several sheets where I need to have the
sum
from all
column d when the criteria in columns c matches a criteria in d12
on
my
"mastersheet". I can add a sumif for every sheet but it seems to
be
unnecessary with you guys around. Also I do not want to edit the
formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

Thanks,
I've been considering this, but I would like to keep my sheets as "clean" as
possible. Is there no way of using the =sum(sheet1:sheet21!a2) with a sumif
statement instead?

"Don Guillett" skrev:

One way.
Put the sumif on each sheet with an indirect reference to d12 of the master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.

One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select alltype the
formula in the cell desiredafter the error msgdelete from the master and
use the sum in para 1.

--
Don Guillett
SalesAid Software

"Hagge" wrote in message
...
I have a workbook with several sheets where I need to have the sum from
all
column d when the criteria in columns c matches a criteria in d12 on my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the formula
every time I add a new sheet. Can you please give me an advice?

Sumif values from different sheets
 

try this where you are summing sheet 2,3,4, & 5

=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5}&"!A1:A17")," a",INDIRECT("Sheet"&{2,3,4
,5}&"!B1:B17")))

--
Don Guillett
SalesAid Software

"Hagge" wrote in message
...
Thanks,
I've been considering this, but I would like to keep my sheets as "clean"
as
possible. Is there no way of using the =sum(sheet1:sheet21!a2) with a
sumif
statement instead?

"Don Guillett" skrev:

One way.
Put the sumif on each sheet with an indirect reference to d12 of the
master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.

One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select alltype
the
formula in the cell desiredafter the error msgdelete from the master
and
use the sum in para 1.

--
Don Guillett
SalesAid Software

"Hagge" wrote in message
...
I have a workbook with several sheets where I need to have the sum
from
all
column d when the criteria in columns c matches a criteria in d12 on
my
"mastersheet". I can add a sumif for every sheet but it seems to be
unnecessary with you guys around. Also I do not want to edit the
formula
every time I add a new sheet. Can you please give me an advice?