Simplify SumIF with multiple sheets

[slm020]

I have looked on the internet for a solution to simplify the following
formula

=SUMIF(Clin1!E34,$A$1,Clin1!$W34:W35)+SUMIF(Clin2!E34,$A$1,Clin2!$W34:W35)+SUMIF(Clin3!E34,$A$1,Clin3!$W34:W35)+SUMIF(Clin4!E34,$A$1,Clin4!$W34:W35)+SUMIF(Clin5!E34,$A$1,Clin5!$W34:W35)+SUMIF(Clin6!E34,$A$1,Clin6!$W34:W35)+SUMIF(Clin7!E34,$A$1,Clin7!$W34:W35)+SUMIF(Clin8!E34,$A$1,Clin8!$W34:W35)+SUMIF(Clin9!E34,$A$1,Clin9!$W34:W35)+SUMIF(Clin10!E34,$A$1,Clin10!$W34:W35)+SUMIF(Clin11!E34,$A$1,Clin11!$W34:W35)+SUMIF(Clin12!E34,$A$1,Clin12!$W34:W35)+SUMIF(Clin13!E34,$A$1,Clin13!$W34:W35)+SUMIF(Clin14!E34,$A$1,Clin14!$W34:W35)+SUMIF(Clin15!E34,$A$1,Clin15!$W34:W35)+SUMIF(Clin16!E34,$A$1,Clin16!$W34:W35)+SUMIF(Clin17!E34,$A$1,Clin17!$W34:W35)+SUMIF(Clin18!E34,$A$1,Clin18!$W34:W35)+SUMIF(Clin19!E34,$A$1,Clin19!$W34:W35)+SUMIF(Clin20!E34,$A$1,Clin20!$W34:W35)+SUMIF(Clin21!E34,$A$1,Clin21!$W34:W35)+SUMIF(Clin22!E34,$A$1,Clin22!$W34:W35)+SUMIF(Clin22!E34,$A$1,Clin23!$W34:W35)+SUMIF(Clin24!E34,$A$1,Clin24!$W34:W35)+SUMIF(Clin25!E34,$A$1,Clin25!$W34:W35)

As you can it is just repeating across consecutive sheets. I have
looked on the internet which has given me sumproduct and various things
however I cannot get this working, can someone demonstrate how to
convert my formula above into a simpler version?

Many Thanks in advance

 

[Don Guillett]

easiest would be to put a sumif on each page and the
=sum(clin1:clin45!a1)

 

[Ragdyer]

First of all, your Sumif formula is wrong!
The range to evaluate *must* be at least the same size as the range to
total.

A *correct*, "simplified" version of your formula *should* look something
like this:

=SUMIF(Clin1!E34:E35,$A$1,Clin1!W34:W35)+...etc.

So, if you could make E34 *and* E35 match your A1, then you might be able to
total your W34 and W35 on all the sheets.
However, you must also make a list of your sheets.
You must then refer to this list in your formula,
OR,
define a name for this list, and use that name in the formula.

Say you enter your sheet names in Z1 to Z25, and name this list "Clin" (no
quotes).

Then you could try something like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Clin&"'!E34:E35"),$A$1,INDIRECT("'"&Clin&"'!W34:W35")))

However, if your scenario *cannot* change, and you must retain your current
set-up,
I would recommend Don's suggestion as definitely being the simplest.

You should however use this type of formula on *each* sheet, and place it in
the *same* cell in each sheet, where your summary sheet is named "Main":

=(Main!A1=E34)*(W34+W35)

And, say you placed all these formulas in A34, then the totaling formula on
your main sheet could be:

=SUM(Clin1:Clin25!A34)

 

[slm020]

Thankyou for your help guys. The sumproduct worked a treat. Thanks for
explaining it all.

Many thanks once again you have saved my fingers and copying and
pasting by at least a day!

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

message
Thankyou for your help guys. The sumproduct worked a treat. Thanks for
explaining it all.

Many thanks once again you have saved my fingers and copying and
pasting by at least a day!