Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with multiple criteria using indirect formula
I have a workbook with numerous worksheets that are summarized on one
worksheet. Each worksheet has a name which is also on the summary worksheet. I am trying to set up an indirect formula within a sumproduct formula that will allow me to pull data from a worksheet with a name that matches the name on my summary worksheet. For example, cell X2 on worksheet NU Summary has the text "Booth" which matches the name of one of the supporting worksheets. My current formula, which works, looks like this: =SUMPRODUCT(--(Booth!$B$3:$B$50149='NU Budget'!$A$1),--(Booth!$B$3:$B$50149=$A5),--(Booth!$C$3:$C$50149='NU Budget'!$B5),--(Booth!$D$3:$D$50149='NU Budget'!$C5),--(Booth!$E$3:$E$50149='NU Budget'!$D5),(Booth!$F$3:$F$50149)) I am trying to make Booth an indirect reference but am having difficulty. Based on other discussions on this board, I came up with the following formula: =SUMPRODUCT(--(INDIRECT(""&X2&"'!$A$3:$A$50149")='NU Budget'!$A$1),--(INDIRECT(""&X2&"'!$B$3:$B$50149")=$A4),--(INDIRECT(""&X2&"'!$C$3:$C$50149")='NU Budget'!$B4),--(INDIRECT(""&X2&"'!$D$3:$D$50149")='NU Budget'!$C4),--(INDIRECT(""&X2&"'!$E$3:$E$50149")='NU Budget'!$D4),(INDIRECT(""&X2&"'!$F$3:$F$50149"))) However, I am getting a "REF!" error message in the cell. Any suggestions on how I can fix this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with multiple criteria using indirect formula
Think you are just missing an apostrophe (')
within the front double quotes part of the INDIRECT: .... --(INDIRECT(" ' " &X2&"'!$A$3:$A$50149")='NU Budget'!$A$1) shown spaced out above for clarity: " ' " Try inserting the apostrophe in all the affected terms, and it should work ok -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "jbo" wrote: I have a workbook with numerous worksheets that are summarized on one worksheet. Each worksheet has a name which is also on the summary worksheet. I am trying to set up an indirect formula within a sumproduct formula that will allow me to pull data from a worksheet with a name that matches the name on my summary worksheet. For example, cell X2 on worksheet NU Summary has the text "Booth" which matches the name of one of the supporting worksheets. My current formula, which works, looks like this: =SUMPRODUCT(--(Booth!$B$3:$B$50149='NU Budget'!$A$1),--(Booth!$B$3:$B$50149=$A5),--(Booth!$C$3:$C$50149='NU Budget'!$B5),--(Booth!$D$3:$D$50149='NU Budget'!$C5),--(Booth!$E$3:$E$50149='NU Budget'!$D5),(Booth!$F$3:$F$50149)) I am trying to make Booth an indirect reference but am having difficulty. Based on other discussions on this board, I came up with the following formula: =SUMPRODUCT(--(INDIRECT(""&X2&"'!$A$3:$A$50149")='NU Budget'!$A$1),--(INDIRECT(""&X2&"'!$B$3:$B$50149")=$A4),--(INDIRECT(""&X2&"'!$C$3:$C$50149")='NU Budget'!$B4),--(INDIRECT(""&X2&"'!$D$3:$D$50149")='NU Budget'!$C4),--(INDIRECT(""&X2&"'!$E$3:$E$50149")='NU Budget'!$D4),(INDIRECT(""&X2&"'!$F$3:$F$50149"))) However, I am getting a "REF!" error message in the cell. Any suggestions on how I can fix this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with multiple criteria using indirect formula
Hi,
Max has already found your apostrophy problem, but I though I would point out that you could simplify this formula by using range names A, B, CC, D, E, F for the ranges on the indirect sheet, and NA, NB, NC, and ND for the references on the Nu Budget sheet: =SUMPRODUCT(--(INDIRECT("`"&X2&"'!$A")=NA),--(INDIRECT("`"&X2&"'!B")=$A4),--(INDIRECT("`"&X2&"'!CC")=NB),--(INDIRECT("`"&X2&"'!D")=NC),--(INDIRECT("`"&X2&"'!E")=ND),(INDIRECT("`"&X2&"'!F" ))) If this helps, please click the Yes button cheers, Shane Devenshire "jbo" wrote: I have a workbook with numerous worksheets that are summarized on one worksheet. Each worksheet has a name which is also on the summary worksheet. I am trying to set up an indirect formula within a sumproduct formula that will allow me to pull data from a worksheet with a name that matches the name on my summary worksheet. For example, cell X2 on worksheet NU Summary has the text "Booth" which matches the name of one of the supporting worksheets. My current formula, which works, looks like this: =SUMPRODUCT(--(Booth!$B$3:$B$50149='NU Budget'!$A$1),--(Booth!$B$3:$B$50149=$A5),--(Booth!$C$3:$C$50149='NU Budget'!$B5),--(Booth!$D$3:$D$50149='NU Budget'!$C5),--(Booth!$E$3:$E$50149='NU Budget'!$D5),(Booth!$F$3:$F$50149)) I am trying to make Booth an indirect reference but am having difficulty. Based on other discussions on this board, I came up with the following formula: =SUMPRODUCT(--(INDIRECT(""&X2&"'!$A$3:$A$50149")='NU Budget'!$A$1),--(INDIRECT(""&X2&"'!$B$3:$B$50149")=$A4),--(INDIRECT(""&X2&"'!$C$3:$C$50149")='NU Budget'!$B4),--(INDIRECT(""&X2&"'!$D$3:$D$50149")='NU Budget'!$C4),--(INDIRECT(""&X2&"'!$E$3:$E$50149")='NU Budget'!$D4),(INDIRECT(""&X2&"'!$F$3:$F$50149"))) However, I am getting a "REF!" error message in the cell. Any suggestions on how I can fix this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with multiple criteria using indirect formula
That worked great. Thanks! Can you explaint to me why that worked? I just
discovered the indirect formula yesterday and, therefore, do not really understand how the apostrophe's and & symbols work to construct the formula. "Max" wrote: Think you are just missing an apostrophe (') within the front double quotes part of the INDIRECT: ... --(INDIRECT(" ' " &X2&"'!$A$3:$A$50149")='NU Budget'!$A$1) shown spaced out above for clarity: " ' " Try inserting the apostrophe in all the affected terms, and it should work ok -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "jbo" wrote: I have a workbook with numerous worksheets that are summarized on one worksheet. Each worksheet has a name which is also on the summary worksheet. I am trying to set up an indirect formula within a sumproduct formula that will allow me to pull data from a worksheet with a name that matches the name on my summary worksheet. For example, cell X2 on worksheet NU Summary has the text "Booth" which matches the name of one of the supporting worksheets. My current formula, which works, looks like this: =SUMPRODUCT(--(Booth!$B$3:$B$50149='NU Budget'!$A$1),--(Booth!$B$3:$B$50149=$A5),--(Booth!$C$3:$C$50149='NU Budget'!$B5),--(Booth!$D$3:$D$50149='NU Budget'!$C5),--(Booth!$E$3:$E$50149='NU Budget'!$D5),(Booth!$F$3:$F$50149)) I am trying to make Booth an indirect reference but am having difficulty. Based on other discussions on this board, I came up with the following formula: =SUMPRODUCT(--(INDIRECT(""&X2&"'!$A$3:$A$50149")='NU Budget'!$A$1),--(INDIRECT(""&X2&"'!$B$3:$B$50149")=$A4),--(INDIRECT(""&X2&"'!$C$3:$C$50149")='NU Budget'!$B4),--(INDIRECT(""&X2&"'!$D$3:$D$50149")='NU Budget'!$C4),--(INDIRECT(""&X2&"'!$E$3:$E$50149")='NU Budget'!$D4),(INDIRECT(""&X2&"'!$F$3:$F$50149"))) However, I am getting a "REF!" error message in the cell. Any suggestions on how I can fix this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with multiple criteria using indirect formula
INDIRECT basically resolves/"activates" concatenated strings into desired
range references. The 2 apostrophes work as a pair (you missed the front ones earlier). They're needed once the sheetname contains a space/s. For sheetnames w/o any spaces, the apostrophes are not needed, but its still fine with the apostrophes inserted. Hence its safer in general to always use the pair of apostrophes. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "jbo" wrote in message ... That worked great. Thanks! Can you explain to me why that worked? I just discovered the indirect formula yesterday and, therefore, do not really understand how the apostrophe's and & symbols work to construct the formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Formula to counts multiple criteria in two columns? | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMIF and SUMPRODUCT with INDIRECT formula problem | Excel Discussion (Misc queries) | |||
sumproduct formula (multiple criteria) | Excel Discussion (Misc queries) | |||
include INDIRECT function into SUMPRODUCT formula | Excel Worksheet Functions |