=SUMPRODUCT not working

[StuartM1]

Hi folks,
I'm trying to add up the figures from a data dump that are a certain Brand, Warehouse, and Receipt Month.

I know my arrays named Brand, Warehouse, Receipt Month and Rec Units are all the correct matching sizes.

Any idea why this formula returns "#VALUE!" ???


=SUMPRODUCT((Brand=$C$3)*(Warehouse=$A9)*(Rcpt_Mo=AA$4)*Rec_Units)

Any ideas are appreciated. I have a similar formula that works fine. I've checked the values in C3, A9 etc. I cant see why it's not working.

Thanks!!!!!!!!!!!!

 

[plshelpexcel]

Try {=SUM((Brand=$C$3)*(Warehouse=$A9)*(Rcpt_Mo=AA$4)*(Rec_Units))}

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1						60
2						60
3	NW		Nabisco	August	1-Aug-18	60
4	Warehouse		Brand	Rcpt_Mo	R_Date	Rec_Units
5	NW		Nabisco	August	20-Aug-18	10
6	SW		Kraft	July	5-Jul-18	20
7	NW		Nabisco	July	10-Jul-18	30
8	SW		Kraft	August	10-Aug-18	40
9	NW		Nabisco	August	5-Aug-18	50
10	SW		Kraft	July	31-Jul-18	60
4c

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(--(Brand=$C3),--(Warehouse=A3),--(R_Date-DAY(R_Date)+1=E3),Rec_Units)
F2	=SUMPRODUCT(--(Brand=$C$3),--(Warehouse=A3),--(Rcpt_Mo=D3),Rec_Units)
F3	=SUMPRODUCT((Brand=$C$3)*(Warehouse=A3)*(Rcpt_Mo=D3)*Rec_Units)

Named Ranges
Name	Refers To	Cells
Brand	='4c'!$C$5:$C$10
R_Date	='4c'!$E$5:$E$10
Rcpt_Mo	='4c'!$D$5:$D$10
Rec_Units	='4c'!$F$5:$F$10
Warehouse	='4c'!$A$5:$A$10

Review the mini examples above.
You did not show an example.

What is in the Rcpt_Mo?

 

[jtakw]

Hi,

Is it possible that you have Text Values in your Rec_Units range thereby causing the #VALUE error?

 

[StuartM1]

Let me check.
I wonder if the arrays have to be right next to each other for it to work. ?
The Rcpt_Mo array just has year/months such as 2018/01 etc.
Thanks guys very much!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1
2						60
3	NW		Nabisco	2018/08		60
4	Warehouse		Brand	Rcpt_Mo		Rec_Units
5	NW		Nabisco	2018/08		10
6	SW		Kraft	2018/07		20
7	NW		Nabisco	2018/07		30
8	SW		Kraft	2018/08		40
9	NW		Nabisco	2018/08		50
10	SW		Kraft	2018/07		60
4cc

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(Brand=$C$3),--(Warehouse=A3),--(Rcpt_Mo=D3),Rec_Units)
F3	=SUMPRODUCT((Brand=C3)*(Warehouse=A3)*(Rcpt_Mo=D3)*Rec_Units)

Named Ranges
Name	Refers To	Cells
'4cc'!Brand	='4cc'!$C$5:$C$10
'4cc'!Rcpt_Mo	='4cc'!$D$5:$D$10
'4cc'!Rec_Units	='4cc'!$F$5:$F$10
'4cc'!Warehouse	='4cc'!$A$5:$A$10

I wonder if the arrays have to be right next to each other for it to work. ? No
The Rcpt_Mo array just has year/months such as 2018/01 etc. As text?

 

[StuartM1]

Thanks Dave. This is nuts. I have exactly what you have and still get a #VALUE .

 

[Dave Patton]

You may have "Text" in your data range "Rec_Units".

Check with =ISNUMBER(F5)

 

[StuartM1]

Got it. Thanks everybody!!! My darn named range included the LABEL above all the values - such as the word Brand, warehouse, etc.

 

[jtakw]

You're welcome.