|
Sums
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 |
| All times are GMT +1. The time now is 11:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com