SUMPRODUCT(SUMIF(INDIRECT Formula not working

[Kclynn]

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
	A	B	C
1	Fruit	Sum of Sales in all regions (Manually type the worksheet name)	Sum of Sales in all regions (Using Ref Sheet)
2	Panel Build	2	#N/A
3	664X33D	11	#N/A
4	GLS-142075-AS	10	#N/A
5	LC-164743	10	#N/A
6	C/146132/MC/20	11	#N/A
7	M/50/LSU/CP	7	#N/A
8	Enclosure #1	8	#N/A
9	Enclosure #2	8	#N/A
10	Enclosure #3	5	#N/A
11	FEN20-16DXP
12	RSM RKM 461-3M/S3059
13	RSSD RSSD 441-5M
Summary

Cell Formulas
Range	Formula
B2:B10	B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!$B$10:$B$37"),A2,INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!$D$10:$D$37")))
C2:C10	C2	=SUMPRODUCT(SUMIF(INDIRECT("'"&RefSheet!$B$3:$B$7&"'!$A$2:$A$10"),A2,INDIRECT("'"&RefSheet!$B$3:$B$9&"'!$D$2:$D$10")))

 

[Kclynn]

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

 

[Fluff]

What is the formula in B2?

 

[steve the fish]

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]

Basically the same t

 

[Fluff]

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")))

 

[Kclynn]

yes

 

[Fluff]

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

 

[Kclynn]

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

How do I do that?

 

[Fluff]

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]

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
	A	B	C
1	Fruit	Sum of Sales in all regions (Manually type the worksheet name)	Sum of Sales in all regions (Using Ref Sheet)
2	Panel Build	0	0
3	664X33D	11	11
4	GLS-142075-AS	3	3
5	LC-164743	0	0
6	C/146132/MC/20	0	0
7	M/50/LSU/CP	0	0
8	Enclosure #1	0	0
9	Enclosure #2	0	0
10	Enclosure #3	0	0
11	FEN20-16DXP	0	0
12	RSM RKM 461-3M/S3059	0	0
13	RSSD RSSD 441-5M	0	0
Summary

Cell Formulas
Range	Formula
B2:B13	B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!$B$10:$B$37"),A2,INDIRECT("'"&{"KIT0041","KIT0042","KIT0001"}&"'!$D$10:$D$37")))
C2:C13	C2	=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
	A	B
1
2
3		KIT0041
4		KIT0042
5		KIT0001
6
Refsheet