Simplify SumIF with multiple sheets

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

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


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"slm020" <slm020.29pm90_1150821902.2751@excelforum-nospam.com> wrote in
message news:slm020.29pm90_1150821902.2751@excelforum-nospam.com...
>
> 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
>
>
> --
> slm020
> ------------------------------------------------------------------------
> slm020's Profile:
> http://www.excelforum.com/member.php...o&userid=35603
> View this thread: http://www.excelforum.com/showthread...hreadid=553721
>

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)

--
HTH,

RD

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

"slm020" <slm020.29pm90_1150821902.2751@excelforum-nospam.com> wrote in
message news:slm020.29pm90_1150821902.2751@excelforum-nospam.com...
>
> 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
>
>
> --
> slm020
> ------------------------------------------------------------------------
> slm020's Profile:
> http://www.excelforum.com/member.php...o&userid=35603
> View this thread: http://www.excelforum.com/showthread...hreadid=553721
>

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!

Thanks for the feed-back.
--

Regards,

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

"slm020" <slm020.29uj3n_1151051101.7888@excelforum-nospam.com> wrote in
message news:slm020.29uj3n_1151051101.7888@excelforum-nospam.com...

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!


--
slm020
------------------------------------------------------------------------
slm020's Profile:
http://www.excelforum.com/member.php...o&userid=35603
View this thread: http://www.excelforum.com/showthread...hreadid=553721