Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error

One way, put all the sheet names (not just first and last) in a range, then
define a name like MyLst or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyLst&"'!H13"),"0" ,INDIRECT("'"&MyLst&"'!F13")))

if the sheet names are in Z1:Z20 you can select that range and type in a
name in the namebox or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z20&"'!H13"),"0 ",INDIRECT("'"&Z1:Z20&"'!F13")))

Regards,

Peo Sjoblom

"Jet" wrote:

Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error

Tried:
=SUMPRODUCT(SUMIF(INDIRECT("'"&P2PH13&"'!H13"),"0 ",INDIRECT("'"&P2PF13&"'!F13")))
after creating names P2PH13 and P2PF13. Please note that excel forced a
specific cell reference when creating the names

Still getting #VALUE error

Thanks for your help on this.



"Peo Sjoblom" wrote:

One way, put all the sheet names (not just first and last) in a range, then
define a name like MyLst or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyLst&"'!H13"),"0" ,INDIRECT("'"&MyLst&"'!F13")))

if the sheet names are in Z1:Z20 you can select that range and type in a
name in the namebox or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z20&"'!H13"),"0 ",INDIRECT("'"&Z1:Z20&"'!F13")))

Regards,

Peo Sjoblom

"Jet" wrote:

Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error

I did not tell you to name 2 cells, you must have misunderstood, put all the
sheet names in a range, example

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6
Sheet7
Sheet8

etc, assume you put them in H1:H8, use that in the formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H8&"'!H13"),"0" ,INDIRECT("'"&H1:H8&"'!F13")))

it works, believe me

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Jet" wrote in message
...
Tried:
=SUMPRODUCT(SUMIF(INDIRECT("'"&P2PH13&"'!H13"),"0 ",INDIRECT("'"&P2PF13&"'!F13")))
after creating names P2PH13 and P2PF13. Please note that excel forced a
specific cell reference when creating the names

Still getting #VALUE error

Thanks for your help on this.



"Peo Sjoblom" wrote:

One way, put all the sheet names (not just first and last) in a range,
then
define a name like MyLst or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyLst&"'!H13"),"0" ,INDIRECT("'"&MyLst&"'!F13")))

if the sheet names are in Z1:Z20 you can select that range and type in a
name in the namebox or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z20&"'!H13"),"0 ",INDIRECT("'"&Z1:Z20&"'!F13")))

Regards,

Peo Sjoblom

"Jet" wrote:

Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error

It sure does, thank you for your help

Kind regards from Montreal!

"Jet" wrote:

Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error

I sure does, thanks for your help

Kind regards from Montreal

"Peo Sjoblom" wrote:

I did not tell you to name 2 cells, you must have misunderstood, put all the
sheet names in a range, example

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6
Sheet7
Sheet8

etc, assume you put them in H1:H8, use that in the formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H8&"'!H13"),"0" ,INDIRECT("'"&H1:H8&"'!F13")))

it works, believe me

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Jet" wrote in message
...
Tried:
=SUMPRODUCT(SUMIF(INDIRECT("'"&P2PH13&"'!H13"),"0 ",INDIRECT("'"&P2PF13&"'!F13")))
after creating names P2PH13 and P2PF13. Please note that excel forced a
specific cell reference when creating the names

Still getting #VALUE error

Thanks for your help on this.



"Peo Sjoblom" wrote:

One way, put all the sheet names (not just first and last) in a range,
then
define a name like MyLst or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyLst&"'!H13"),"0" ,INDIRECT("'"&MyLst&"'!F13")))

if the sheet names are in Z1:Z20 you can select that range and type in a
name in the namebox or use the range itself

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z20&"'!H13"),"0 ",INDIRECT("'"&Z1:Z20&"'!F13")))

Regards,

Peo Sjoblom

"Jet" wrote:

Trying to sum all F13 cells on multiple worksheets where H13 is 0
Tried =IF('Store # 85:[Store # (35)]Store # (35)'!H130,SUM('Store #
85:[Store # (35)]Store # (35)'!F13)) and getting a #REF error