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]

PickKitVer1.xlsm
	A	B	C	D	E
7
8
9	CODE	PART #	DESCRIPTION	QTY
10	A2	664X33D	Manual Motor Controller/Disconnects,Nema 4X Enclosed Rotary Switch, Bryant	3
11	A1	2222	Material and labor to assemble 4 MainContol Panels and 4 HMI Pedestals perACS Quote Q10368	2
12	A140	E0005536	ORING,3/16 DIA X 9.5" HT BLUE, 3"CTRS, ITR	2
13	A3	GLS-142075-AS	IDEM safety switch, cable-pull interlock,with reset, 80m, emergency stop, (3)N.C. safety output(s), (1) N.O.monitoring output(s), 24 VDC LED, diecastaluminum body, (3) 1/2in NPT cableentries.	1
14
15
16
17
KIT0001

 

[Fluff]

One possibility is, your range on the RefSheet varies.

 

[Kclynn]

I did change that now the N/A just show as #REF!

 

[Fluff]

Are both formulae meant to be doing the same thing?

 

[Kclynn]

Yes

 

[Kclynn]

Also if I take the formula showing the individual tab names and try to change it to a range that didn't work for me either. I have been trying most of the week to get this working and I am just frustrated.

 

[steve the fish]

For sure you will get na error if the sheet name ranges are not the same size. You will get a ref error if none exist.

 

[steve the fish]

Actually that shouldnt say none it should say any of them dont exist or any of the cells are blank.

 

[Kclynn]

I don't see anything in the formula that does not exist.