sumproduct returns value error or zero

[Twiggy]

Hi Excel Ninjas,
The sumproduct formula I'm using is returning value errors or zeros.
I'm confirming the formula with ctrl-shift-enter.
I've used this formula in a similar file, but I haven't been able to figure out why it isn't working here.
My expected results are sheet1, cell O11 = 20160.
My expected results are sheet1, cell P11 = 60320.
I would appreciate your help resolving this.

 

[John Jairo V]

Hi @Twiggy

You could use this formulae:

=SUMPRODUCT(--($A11=Table1[[WAREHOUSE]:[WAREHOUSE]]),--($C11=Table1[[SHIPTO_NUMBER]:[SHIPTO_NUMBER]]),--($E11=Table1[[PRODUCT_NUMBER]:[PRODUCT_NUMBER]]),INDEX(Table1[[2016*01]:[2016*52]],,MATCH(O$9,Table1[[#Headers],[2016*01]:[2016*52]],)))

Or This (Better):

=SUMIFS(INDEX(Table1[[2016*01]:[2016*52]],,MATCH(O$9,Table1[[#Headers],[2016*01]:[2016*52]],)),Table1[[WAREHOUSE]:[WAREHOUSE]],$A11,Table1[[SHIPTO_NUMBER]:[SHIPTO_NUMBER]],$C11,Table1[[PRODUCT_NUMBER]:[PRODUCT_NUMBER]],$E11)

Both formulas not need CSE (Ctrl + Shift + Enter). You must convert the Column C and E columns of Sheet2 into numbers (are text in this moment). Blessings!

 

[Hui]

You can use the normal formula
O11: =SUMPRODUCT((Table1[WAREHOUSE]=$A11)*(Table1[SHIPTO_NUMBER]=$C11)*(Table1[PRODUCT_NUMBER]=$E11)*(Table1[[#Headers],[2016*01]:[2016*52]]=O$9)*(Table1[[2016*01]:[2016*52]]))

One issue is that the values in Sheet2 Columns C & E are text and not numbers
Either convert them to numbers or change the numbers in sheet1 cells C11 & E11 to text by placing a ' in front of them eg: '13458

 

[John Jairo V]

You can use the normal formula
O11: =SUMPRODUCT((Table1[WAREHOUSE]=$A11)*(Table1[SHIPTO_NUMBER]=$C11)*(Table1[PRODUCT_NUMBER]=$E11)*(Table1[[#Headers],[2016*01]:[2016*52]]=O$9)*(Table1[[2016*01]:[2016*52]]))

Hi @Hui!

What happened if this formulae is drag into the right?
Check it please!

 

[Hui]

It works fine?

 

[John Jairo V]

If you copy and paste... the structural references working well... but if you drag into the right, the structural references moves. Check it please and comment!

 

[Hui]

I'm a keyboard junkie
and worse than that I rarely use Tables
Despite all Tables good features, the inconsistancys in implementation annoys me

Having to use:
=SUMPRODUCT((Table1[[WAREHOUSE]:[WAREHOUSE]]=$A11)*(Table1[[SHIPTO_NUMBER]:[SHIPTO_NUMBER]]=$C11)*(Table1[[PRODUCT_NUMBER]:[PRODUCT_NUMBER]]=$E11)*(Table1[[#Headers],[2016*01]:[2016*52]]=O$9)*(Table1[[2016*01]:[2016*52]]))

to lock a Table's Column seems wrong

when either:
=SUMPRODUCT((Table1[$WAREHOUSE]=$A11)*(Table1[$SHIPTO_NUMBER]=$C11)*(Table1[$PRODUCT_NUMBER]=$E11)*(Table1[[#Headers],[2016*01]:[2016*52]]=O$9)*(Table1[[2016*01]:[2016*52]]))

or

=SUMPRODUCT((Table1[WAREHOUSE]=$A11)*(Table1[SHIPTO_NUMBER]=$C11)*(Table1[PRODUCT_NUMBER]=$E11)*(Table1[[#Headers],[2016*01]:[2016*52]]=O$9)*(Table1[[2016*01]:[2016*52]]))

Should work when dragged

But only the 2nd works when it is copy/pasted

 

[John Jairo V]

Despite all Tables good features, the inconsistancys in implementation annoys me

Yeah! Is a bad thing when you work with structural references. Microsoft must check it. Blessings!

 

[bosco_yip]

Or....

In O11, formula drag into the right

=SUMIFS(Sheet2!H:H,Sheet2!$A:$A,$A11,Sheet2!$C:$C,$C11,Sheet2!$E:$E,$E11)

Regards
Bosco