| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Multiple W/Sheets
=SUMPRODUCT(--(A3=ALB!$A$2:$A$2000))
I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct Multiple W/Sheets
With your sheetnames in range G1:G17 try the below formula
=SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: =SUMPRODUCT(--(A3=ALB!$A$2:$A$2000)) I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct Multiple W/Sheets
Tip:
=SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) Whenever you quote a range inside INDIRECT there's no need to include the $ signs. Since the range is evaluated as a text string it will *never* change if the formula is copied. Makes it a bit easier to read and saves a couple of keystrokes. =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!A2:A2000"),A3)) -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... With your sheetnames in range G1:G17 try the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: =SUMPRODUCT(--(A3=ALB!$A$2:$A$2000)) I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct Multiple W/Sheets
If I just type the sheet name as per the tab into G1:G17 I get a #ref error
I paste the formula into column c, to give me a result from col a. Thanks for assistance. "Jacob Skaria" wrote: With your sheetnames in range G1:G17 try the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: =SUMPRODUCT(--(A3=ALB!$A$2:$A$2000)) I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct Multiple W/Sheets
The sheet name range G1:G17 should not be blank or invalid. Try with 3 valid
sheetnames with the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G3 & "'!A2:A2000"),A3)) Biff thanks; I noticed this but just worked on OP's formula...(my laziness) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: If I just type the sheet name as per the tab into G1:G17 I get a #ref error I paste the formula into column c, to give me a result from col a. Thanks for assistance. "Jacob Skaria" wrote: With your sheetnames in range G1:G17 try the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: =SUMPRODUCT(--(A3=ALB!$A$2:$A$2000)) I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct Multiple W/Sheets
Thanks Jacob, It worked like a treat when I realised that I was counting a
lot of 1s and 2s and it wasn't an error. I'm saving this formula in my 'book of handy hints' "Jacob Skaria" wrote: The sheet name range G1:G17 should not be blank or invalid. Try with 3 valid sheetnames with the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G3 & "'!A2:A2000"),A3)) Biff thanks; I noticed this but just worked on OP's formula...(my laziness) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: If I just type the sheet name as per the tab into G1:G17 I get a #ref error I paste the formula into column c, to give me a result from col a. Thanks for assistance. "Jacob Skaria" wrote: With your sheetnames in range G1:G17 try the below formula =SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3)) If this post helps click Yes --------------- Jacob Skaria "AussieBec" wrote: =SUMPRODUCT(--(A3=ALB!$A$2:$A$2000)) I have the above formula over 1000 lines which I need to sort the result ascending/descending of those 1000 lines some point to different worksheets so when I sort they no longer point to the correct worksheet. How do I use a sumproduct to reference all worksheets (up to 17 worksheets) incidently all will have the same range A2:A2000 just different worksheet name. ALB.UNKN (worksheet alb) BAT (worksheet bat) BAT.001 (worksheet bat) BLA (worksheet bla) BLA.001 (worksheet bla) CRE (worksheet cre) CRE.001 (worksheet cre) I'm sure they will be a solution quicker than I typed this request. Thanks Bec |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT FOR MULTIPLE SHEETS | Excel Discussion (Misc queries) | |||
| SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
| Sumproduct on multiple sheets...please help??? | Excel Worksheet Functions | |||
| sumproduct for multiple sheets | Excel Worksheet Functions | |||
| sumproduct from multiple sheets | Excel Worksheet Functions | |||
Linear Mode
