# SUMPRODUCT(SUMIF(INDIRECT Formula not working

#### Kclynn

##### Board Regular
Can anyone tell me why the formula in column C on the summary tab does not work? I am adding the summary sheet and one of 4 of the worksheets.
PickKitVer1.xlsm
ABC
1FruitSum of Sales in all regions (Manually type the worksheet name)Sum of Sales in all regions (Using Ref Sheet)
2Panel Build2#N/A
3664X33D11#N/A
4GLS-142075-AS10#N/A
5LC-16474310#N/A
6C/146132/MC/2011#N/A
7M/50/LSU/CP7#N/A
8Enclosure #18#N/A
9Enclosure #28#N/A
10Enclosure #35#N/A
11FEN20-16DXP
12RSM RKM 461-3M/S3059
Summary
Cell Formulas
RangeFormula
B2:B10B2=SUMPRODUCT(SUMIF(INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!\$B\$10:\$B\$37"),A2,INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!\$D\$10:\$D\$37")))
C2:C10C2=SUMPRODUCT(SUMIF(INDIRECT("'"&RefSheet!\$B\$3:\$B\$7&"'!\$A\$2:\$A\$10"),A2,INDIRECT("'"&RefSheet!\$B\$3:\$B\$9&"'!\$D\$2:\$D\$10")))

#### Kclynn

##### Board Regular
Columns "B" and "C" should end up with the same results.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

##### MrExcel MVP, Moderator
What is the formula in B2?

#### steve the fish

##### Well-known Member
Only way i can think thats possible is if some of your sheets have reference errors in column D where the 'fruit' is located in column A.

#### Kclynn

##### Board Regular

Basically the same t

#### Fluff

##### MrExcel MVP, Moderator

Are the ranges on the Refsheet locked like
=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!\$B\$3:\$B\$8&"'!\$B\$10:\$B\$37"),A2,INDIRECT("'"&Refsheet!\$B\$3:\$B\$8&"'!\$D\$10:\$D\$37")))

yes

#### Fluff

##### MrExcel MVP, Moderator

In that case I have no idea. Could you share your workbook?

#### Kclynn

##### Board Regular
In that case I have no idea. Could you share your workbook?
How do I do that?

#### Fluff

##### MrExcel MVP, Moderator
You would need to upload it to a share site such as OneDrive, Dropbox, GoogleDrive, then mark for sharing & post the link to the thread.

#### KRice

##### Well-known Member
This seems to work for three "KIT" sheets and a Refsheet listing just those three sheets, and then adjusting the C2 formula to include only the Refsheet range listing actual sheet references (to avoid an error)...see the portion in the C2 formula where that range was adjusted (Refsheet!\$B\$3:\$B\$5 in two places):
MrExcel20200424.xlsx
ABC
1FruitSum of Sales in all regions (Manually type the worksheet name)Sum of Sales in all regions (Using Ref Sheet)
2Panel Build00
3664X33D1111
4GLS-142075-AS33
5LC-16474300
6C/146132/MC/2000
7M/50/LSU/CP00
8Enclosure #100
9Enclosure #200
10Enclosure #300
11FEN20-16DXP00
12RSM RKM 461-3M/S305900
Summary
Cell Formulas
RangeFormula
B2:B13B2=SUMPRODUCT(SUMIF(INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!\$B\$10:\$B\$37"),A2,INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!\$D\$10:\$D\$37")))
C2:C13C2=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!\$B\$3:\$B\$5&"'!\$b\$10:\$b\$37"),A2,INDIRECT("'"&Refsheet!\$B\$3:\$B\$5&"'!\$D\$10:\$D\$37")))

MrExcel20200424.xlsx
AB
1
2
3KIT0041
4KIT0042
5KIT0001
6
Refsheet

Replies
1
Views
255
Replies
5
Views
182
Replies
0
Views
251
Replies
4
Views
195
Replies
6
Views
482

1,130,201
Messages
5,640,821
Members
417,169
Latest member
StumpoC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back