SUMPRODUCT Formula returning #VALUE!

[Robnrolla]

Please can someone help me with this formula issue - =SUMPRODUCT((Table2[Department]=[@Department])*(Months=[@Month])*(Table2[[Month 1]:[Month 12]]))

Notes -

1) The Second Array is the Month dates which are outside and above data table 2.

2) The Third array is the 36 cells of data.

3) The dates are in custom format "MMM-YY" but are actually in date format behind the scenes i.e - 01/01/2018.

Data Table 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Department[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD]Warehouse[/TD]
[TD]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

Data Table 2
Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Month 7[/TD]
[TD]Month 8[/TD]
[TD]Month 9[/TD]
[TD]Month 10[/TD]
[TD]Month 11[/TD]
[TD]Month 12[/TD]
[/TR]
[TR]
[TD]Warehouse[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Warehouse[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Warehouse[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

All the dimensions are correct to my knowledge so therefore i am not sure what the problem is.


Please can someone help me?

Thank you,
Rob

 

[Marcelo Branco]

Is the array MONTHS (named range) aligned with the headers Month 1, Month 2,...Month 12?
If so your formula should work.

M.

 

[Robnrolla]

Hello,

Yes everything is lined up which is why i cannot understand what is going on. can you upload MS Excel files? It is hard to show example in the thread as per above. Basically array 1 is from the 1st Warehouse to the 3rd warehouse in the department column. the second array is from Jan-18 too Dec-18 which are above Month 1-12 and the final array is the range which is Warehouse month 1=4 to Warehouse Month 12=7. Hope that makes sense?

thanks

 

[Marcelo Branco]

Worked for me. (months in Portuguese: fev-18 = feb-18; mai-18 = may-18; etc)
Named range Months: F1:Q1


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

jan-18​

[/TD]
[TD]

fev-18​

[/TD]
[TD]

mar-18​

[/TD]
[TD]

abr-18​

[/TD]
[TD]

mai-18​

[/TD]
[TD]

jun-18​

[/TD]
[TD]

jul-18​

[/TD]
[TD]

ago-18​

[/TD]
[TD]

set-18​

[/TD]
[TD]

out-18​

[/TD]
[TD]

nov-18​

[/TD]
[TD]

dez-18​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Month​

[/TD]
[TD]

Department​

[/TD]
[TD]

Amount​

[/TD]
[TD][/TD]
[TD]

Department​

[/TD]
[TD]

Month 1​

[/TD]
[TD]

Month 2​

[/TD]
[TD]

Month 3​

[/TD]
[TD]

Month 4​

[/TD]
[TD]

Month 5​

[/TD]
[TD]

Month 6​

[/TD]
[TD]

Month 7​

[/TD]
[TD]

Month 8​

[/TD]
[TD]

Month 9​

[/TD]
[TD]

Month 10​

[/TD]
[TD]

Month 11​

[/TD]
[TD]

Month 12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

jan-18​

[/TD]
[TD]

Warehouse​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD]

Warehouse​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

fev-18​

[/TD]
[TD]

Warehouse​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD]

Warehouse​

[/TD]
[TD]

1​

[/TD]
[TD]

8​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Warehouse​

[/TD]
[TD]

7​

[/TD]
[TD]

10​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C3 (Table1)
=SUMPRODUCT((Table2[Department]=[@Department])*(Months=[@Month])*Table2[[Month 1]:[Month 12]])

M.

 

[Robnrolla]

So in the formula i am using if translated to your workings above would be: =SUMPRODUCT(($E$2:$E$5=$B$3)*($F$1:$Q$1=$A$3)*($F$3:$Q$5))

The dates are in 01/01/2018 but in custom "mmm-YY" format. Everything else is General. Really don't understand why it is not working? To re-iterate, the Dates above the Month 1- Month 12 are outside Table 2. This normally works for 2 array formula's based on single row range.

Thanks

 

[Marcelo Branco]

So in the formula i am using if translated to your workings above would be: =SUMPRODUCT(($E$2:$E$5=$B$3)*($F$1:$Q$1=$A$3)*($F$3:$Q$5))

You formula is not correct. It should be
=SUMPRODUCT(($E$3:$E$5=$B$3)*($F$1:$Q$1=$A$3)*($F$3:$Q$5))

M.