Sumifs only displaying #value in table lookup

[lost_in_the_sauce]

Have a data pull for financials, trying to make a repeatable store summary. $ values are on "Data" tab. Trying to collect by 3 categories - month, store name.

=SUMIFS(Data!$J$2:$P$1214,Data!$B$2:$B$1214,Sheet1!$B3,Data!$J$1:$P$1,Sheet1!C$2,Data!$F$2:$F$1214,Sheet1!B2)

Values are in cells J2:P1214, categories are F1:P1, months are B, store is F. So from the table, is should sumifs numbers that have the same store in F, the same month in B, and the same category in row 1. Keep getting the #value error though.

 

[lost_in_the_sauce]

If you mean that you only need to sum 1 column from J:P and want to choose that column with INDEX then yes, you could try something like

=SUMIFS(INDEX(Data!$J$2:$P$1214,0,MATCH(Sheet1!C$2,Data!$J$1:$P$1,0))Data!$B$2:$B$1214,Sheet1!$B3,Data!$F$2:$F$1214,Sheet1!B2)

If you can have multiple rows and columns that meet the criteria then you need sumproduct.

the sumproduct one works, I'm getting an error here

 

[jasonb75]

Sorry, missing a comma

=SUMIFS(INDEX(Data!$J$2:$P$1214,0,MATCH(Sheet1!C$2,Data!$J$1:$P$1,0)),Data!$B$2:$B$1214,Sheet1!$B3,Data!$F$2:$F$1214,Sheet1!B2)