You're more than welcome Shawn
I hadn't thought about the possibility that column O contained Text!!!
Glad you figured out the workaround to the problem.
--
Regards
Roger Govier
"SixBowls" wrote in message
...
column o has text and the formula gives me an error. I got it to work by
changing the formula to:
=SUMPRODUCT((INDIRECT(C3))*(MOD(COLUMN(INDIRECT(C3 )),2)=1))+SUMPRODUCT((INDIRECT(D3))*(MOD(COLUMN(IN DIRECT(D3)),2)=1))
c3 is sheet name + c9:m9 and d3 is sheet name + q9:u9
Thanks for the help!
Shawn
"Roger Govier" wrote:
Hi
Well then, you could just use
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))-INDIRECT(A2&"!O9")
Taking the single value of O9 away from the total would still be far more
efficient than adding up all of the individual cells.
--
Regards
Roger Govier
"SixBowls" wrote in message
...
Is this possible with INDIRECT? The following formula works:
=SUM(Sep09:Jan09!C9)
I would like to change it to something like this (is returning a #REF!
error):
=SUM(INDIRECT(B3&":"&B4&"!"&B5))
b3 is Sep09, b4 is Jan09, b5 is C9
If I could get this to work, I can plug the rest of the formula.
"SixBowls" wrote:
The formula is correct. I do not use column O.
"Roger Govier" wrote:
Hi
Are you sure that the formula you posted is correct?
It looks as though you are taking every other column between C and
U,
except
you have missed out column O
If you are taking every other column from the relevant sheet, then
it
could
be written as
=SUMPRODUCT((Sep!C9:U9)*MOD(COLUMN(Sep!C9:U9),2)=1 ))
Then, using a Summary sheet, enter in
cell A1 !C9:U9
in A2 Sep
in A3 Oct
in A4 Nov
In B2 =A2&$A$1
Copy down through cells B3:B4
in C2 you could enter
=SUMPRODUCT((INDIRECT(B2))*(MOD(COLUMN(INDIRECT(B2 )),2)=1))
and copy down to C3:C4
Your result required would be =SUM(C2:C4)
--
Regards
Roger Govier
"SixBowls" wrote in message
...
I have the following formula that totals data for multiple tabs.
Each tab
is
a month and each tab is set-up the same.
=SUM('Sep-09:Jan-09'!C9,'Sep-09:Jan-09'!E9,'Sep-09:Jan-09'!G9,'Sep-09:Jan-09'!I9,'Sep-09:Jan-09'!K9,'Sep-09:Jan-09'!M9,'Sep-09:Jan-09'!Q9,'Sep-09:Jan-09'!S9,'Sep-09:Jan-09'!U9)
The formula works but I have 196 rows where the formula is used.
I
currently do a find/replace when I add a new month or want to look
at
a
quarter. I would like the formula to reference two cells (b3 for
what is
now
Sep-09 and b4 for what is now Jan-09) and be able to type the
sheet
name
in
those cells to get the period.
Also open to suggestions on a better formula.
__________ Information from ESET Smart Security, version of virus
signature database 4536 (20091023) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4537 (20091023) __________
The message was checked by ESET Smart Security.
http://www.eset.com
.
__________ Information from ESET Smart Security, version of virus
signature database 4545 (20091026) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 4545 (20091026) __________
The message was checked by ESET Smart Security.
http://www.eset.com
.
__________ Information from ESET Smart Security, version of virus
signature database 4549 (20091027) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 4549 (20091027) __________
The message was checked by ESET Smart Security.
http://www.eset.com