sumifs + offset formula issue

hey all

having some trouble with sumifs + offset formula. i have my data where it shows quantity by 2015, 2014, 2013.

Through a drop down in "sheet1" cell C17, i want to be able to show the quantity by month for states/provinces.

in "sheet1", cell D21 this is my formula:

Please Login or Register  to view this content.

it is giving me #value error...can someone pls help?

thank you - attached is the sample file.

with the offset, i am trying to make it go down 3 rows and then use match formula to "sum" the range i.e. 2014 column or 2015 column...

basically, in C17, if i select "Year 2015", i want it go the respective range in the "data" sheet ie. column K and sum it, based on the state name and month.

pls help!

Originally Posted by mcmahobt

In any case, try this formula instead. The results look accurate, but I'll let you be the judge. Paste into D21 as an array formula and confirm with CTRL+SHIFT+ENTER. Drag across and down to fill:

Please Login or Register  to view this content.

Hello

i tried this formula...however i do not think it is capturing the correct answer.

for example,
If i select "Year PY 2014" in cell C17 and for "alberta" for January, the formula shows 4295.52 (that is incorrect as it should be a whole number, so i assume it is looking at a wrong column).

if i manual filter in the "Data" sheet, based on the above criteria, the answer should be 24,179.

thank you

Originally Posted by jw01

Hello

i tried this formula...however i do not think it is capturing the correct answer.

for example,
If i select "Year PY 2014" in cell C17 and for "alberta" for January, the formula shows 4295.52 (that is incorrect as it should be a whole number, so i assume it is looking at a wrong column).

if i manual filter in the "Data" sheet, based on the above criteria, the answer should be 24,179.

thank you

I figured out what the issue was with your formula:

the "match" range should have been this:
MATCH($C$17&$C$18,Data!$K$61:$V$61,0)))

so formula should be in D21
=SUMPRODUCT(--(($C21=Data!$D$62:$D$7225)*(D$19=Data!$B$62:$B$7225)),INDEX(Data!$K$62:$V$7225,,MATCH($C$17&$C$18,Data!$K$61:$V$61,0)))

It gives me the answer i need w/o using array, so i am not sure if i need array?