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?

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?

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?

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?

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?

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?

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?