sumifs/sumproduct formula returns VALUE error

Hello Everyone

I get stuck with one particular issue ...

I'm trying to SUMIFS (or SUMPRODUCT with criteria) however I get VALUE error


I got spreadsheet with over 500 rows (material) and 21 columns (months) and I' trying to get a sum of the products in specific period of time - for instance 2016

I have used below array formulas however excel returns VALUE error

=SUMIFS($B$2:$U$576, $A$2:$A$576, A2, $B$1:$U$1, ">="&DATE(2016, 1,1), $B$1:$U$1, "<="&DATE(2016, 12, 31))

=SUMPRODUCT(--($B$1:$U$1>=DATE(2016, 1, 1)), --($B$1:$U$1<=DATE(2016, 12, 31)), $B$2:$U$576, $A$2:$A$576)

both returns VALUE error

Where do I make a mistake :/

Thanks in advance for your support

I changed the SP formula in X2 to this:

=SUMPRODUCT(($B$1:$U$1>=DATE(2016, 1, 1))*($B$1:$U$1<=DATE(2016, 12, 31))*($A$2:$A$576=A2), $B$2:$U$576)

and it returned a number. You did not set a criteria for column A.

Hope this helps.

Pete

what do you want to do, every value in column A is unique, is this representitive of your data?

if it is
your sumproduct could be
=SUMPRODUCT(--($B$1:$U$1>=DATE(2016, 1, 1)), --($B$1:$U$1<=DATE(2016, 12, 31)), B2:U2)

Originally Posted by Pete_UK

I changed the SP formula in X2 to this:

=SUMPRODUCT(($B$1:$U$1>=DATE(2016, 1, 1))*($B$1:$U$1<=DATE(2016, 12, 31))*($A$2:$A$576=A2), $B$2:$U$576)

and it returned a number. You did not set a criteria for column A.

Hope this helps.

Pete

Hi Pete

amazing ... works
thanks!!!

Just wondering why sumifs does not work thou

There is an invalid value in R13

Try

=SUMPRODUCT(--(YEAR($B$1:F$1)=2016)*($B$2:F$576)*($A$2:$A$576=$A2))

SUMIFS will not work as all ranges have be the same size.

Originally Posted by JohnTopley

There is an invalid value in R13

Try

=SUMPRODUCT(--(YEAR($B$1:F$1)=2016)*($B$2:F$576)*($A$2:$A$576=$A2))

SUMIFS will not work as all ranges have be the same size.

Hi John
thanks ...
this works as well

Thank you all for support