sumifs + offset formula issue

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?