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

 

[Fluff]

How about
=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!$B$3:$B$5&"'!$B$7:$B$37"),A2,INDIRECT("'"&Refsheet!$B$3:$B$5&"'!$D$10:$D$37")))

 

[steve the fish]

=SUMPRODUCT(SUMIF(INDIRECT("'"&RefSheet!$B$3:$B$7&"'!$A$2:$A$10"),A2,INDIRECT("'"&RefSheet!$B$3:$B$9&"'!$D$2:$D$10")))

In this one your RefSheet ranges are different sizes. You cant do that. You will get na.

Lets say you change it to B3:B7. All of those cells must have a valid sheet name in them. None can be empty.

 

[Kclynn]

I am actually getting a number but it is not the right number

 

[Kclynn]

How about
=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!$B$3:$B$5&"'!$B$7:$B$37"),A2,INDIRECT("'"&Refsheet!$B$3:$B$5&"'!$D$10:$D$37")))

I am actually getting a number but it is not the right number

 

[Kclynn]

I am actually getting a number but it is not the right number

When i insert the formula you supplied

 

[Fluff]

How many sheet names do you have on the refsheet?

 

[Kclynn]

 

[Kclynn]

There will be more sheets added in many cases

 

[Fluff]

Ok, how about
=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!$B$3:$B$8&"'!$B$10:$B$37"),A2,INDIRECT("'"&Refsheet!$B$3:$B$8&"'!$D$10:$D$37")))

 

[Kclynn]

Ok, how about
=SUMPRODUCT(SUMIF(INDIRECT("'"&Refsheet!$B$3:$B$8&"'!$B$10:$B$37"),A2,INDIRECT("'"&Refsheet!$B$3:$B$8&"'!$D$10:$D$37")))

This is the result from that formulaa