software

I am trying to use the following formula to sum the same cell on multiple
sheets IF the critria in another cell on each sheet matches :

=SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)

I get a return of VALUE.
Can I not use SUMIF to calculate the numbers I need?
Any suggestions?


=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I6quot;),quot;NPquot;, INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I8quot;)
))

where C1:C2 is a range housing the relevant sheetnames in separate cells.--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;frustratedwthisquot; gt; wrote in
message ...
gt; I am trying to use the following formula to sum the same cell on multiple
gt; sheets IF the critria in another cell on each sheet matches :
gt;
gt; =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)
gt;
gt; I get a return of VALUE.
gt; Can I not use SUMIF to calculate the numbers I need?
gt; Any suggestions?
I tried the formula and it returns REF.
Any other suggestions? and THANKS for you r help!!

quot;Bob Phillipsquot; wrote:

gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I6quot;),quot;NPquot;, INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I8quot;)
gt; ))
gt;
gt; where C1:C2 is a range housing the relevant sheetnames in separate cells.
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;frustratedwthisquot; gt; wrote in
gt; message ...
gt; gt; I am trying to use the following formula to sum the same cell on multiple
gt; gt; sheets IF the critria in another cell on each sheet matches :
gt; gt;
gt; gt; =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)
gt; gt;
gt; gt; I get a return of VALUE.
gt; gt; Can I not use SUMIF to calculate the numbers I need?
gt; gt; Any suggestions?
gt;
gt;
gt;

I hate to post this way, but this question is answered within this site.
Just keep searching for it. I wasn't able to use one thread, had to combine
a couple different ones to get mine to work, but I eventually did. Sorry,
but i don't have the formula anymore. I had a problem making sure I had
comma's and spaces in the formula correct. Keep playing with it and you will
get it.

quot;frustratedwthisquot; wrote:

gt; I tried the formula and it returns REF.
gt; Any other suggestions? and THANKS for you r help!!
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I6quot;),quot;NPquot;, INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I8quot;)
gt; gt; ))
gt; gt;
gt; gt; where C1:C2 is a range housing the relevant sheetnames in separate cells.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;frustratedwthisquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; I am trying to use the following formula to sum the same cell on multiple
gt; gt; gt; sheets IF the critria in another cell on each sheet matches :
gt; gt; gt;
gt; gt; gt; =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)
gt; gt; gt;
gt; gt; gt; I get a return of VALUE.
gt; gt; gt; Can I not use SUMIF to calculate the numbers I need?
gt; gt; gt; Any suggestions?
gt; gt;
gt; gt;
gt; gt;

Bob's formula will work but you need to put ALL sheet names in a range so if
you have 10 sheet you would need a ten ccell range to refer to like C1::C10,
so put all sheet names in the range, then all the apostrophes (for sheet
names with spaces) should be there just like in Bob's formula so if you want
to sum I8 in 2 sheets where I6 is quot;NPquot; then Bob's formula is correct. There
is a 3D SUMIF example for download using other sheet names but the same
technique here

nwexcelsolutions.com/Download...e sheets.xlsRegards,

Peo Sjoblomquot;frustratedwthisquot; wrote:

gt; I tried the formula and it returns REF.
gt; Any other suggestions? and THANKS for you r help!!
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I6quot;),quot;NPquot;, INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I8quot;)
gt; gt; ))
gt; gt;
gt; gt; where C1:C2 is a range housing the relevant sheetnames in separate cells.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;frustratedwthisquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; I am trying to use the following formula to sum the same cell on multiple
gt; gt; gt; sheets IF the critria in another cell on each sheet matches :
gt; gt; gt;
gt; gt; gt; =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)
gt; gt; gt;
gt; gt; gt; I get a return of VALUE.
gt; gt; gt; Can I not use SUMIF to calculate the numbers I need?
gt; gt; gt; Any suggestions?
gt; gt;
gt; gt;
gt; gt;

Sorry, I only tested with 2 sheets, hence C1 and C2. As Peo says, you should
list all the target sheets in C1:Cn, and adjust the formula to suit.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;frustratedwthisquot; gt; wrote in
message ...
gt; I tried the formula and it returns REF.
gt; Any other suggestions? and THANKS for you r help!!
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt;
gt; gt;
=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I6quot;),quot;NPquot;, INDIRECT(quot;'quot;amp;C1:C2amp;quot;'!I8quot;)
gt; gt; ))
gt; gt;
gt; gt; where C1:C2 is a range housing the relevant sheetnames in separate
cells.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;frustratedwthisquot; gt; wrote in
gt; gt; message ...
gt; gt; gt; I am trying to use the following formula to sum the same cell on
multiple
gt; gt; gt; sheets IF the critria in another cell on each sheet matches :
gt; gt; gt;
gt; gt; gt; =SUMIF('06-01:06-92'I6,NP,'06-01:06-92I8)
gt; gt; gt;
gt; gt; gt; I get a return of VALUE.
gt; gt; gt; Can I not use SUMIF to calculate the numbers I need?
gt; gt; gt; Any suggestions?
gt; gt;
gt; gt;
gt; gt;