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.

 

[Fluff]

You cannot use sumifs like that, try

=SUMPRODUCT((Data!$B$2:$B$1214=$B3)*(Data!$J$1:$P$1=C$2)*(Data!$F$2:$F$1214=B2)*(Data!$J$2:$P$1214))

 

[lost_in_the_sauce]

could I index match with the J2:P1214 array of values and multiple matches?

 

[jasonb75]

In case you're wondering exactly what it is that you have done wrong by trying to use sumifs, the criteria ranges (Data!$B$2:$B$1214, Data!$J$1:$P$1, and Data!$F$2:$F$1214) must all contain the exact same number of rows and columns as the sum range (Data!$J$2:$P$1214), not the same number of rows or columns.

 

[lost_in_the_sauce]

In case you're wondering exactly what it is that you have done wrong by trying to use sumifs, the criteria ranges (Data!$B$2:$B$1214, Data!$J$1:$P$1, and Data!$F$2:$F$1214) must all contain the exact same number of rows and columns as the sum range (Data!$J$2:$P$1214), not the same number of rows or columns.

am I not? 2-1214 rows and columns J-P?

 

[lost_in_the_sauce]

In case you're wondering exactly what it is that you have done wrong by trying to use sumifs, the criteria ranges (Data!$B$2:$B$1214, Data!$J$1:$P$1, and Data!$F$2:$F$1214) must all contain the exact same number of rows and columns as the sum range (Data!$J$2:$P$1214), not the same number of rows or columns.

nevermind, reread it

 

[Fluff]

could I index match with the J2:P1214 array of values and multiple matches?

Depends on exactly what you are trying to do & what your data is like. Did you try the formula I suggested?

 

[lost_in_the_sauce]

You cannot use sumifs like that, try

=SUMPRODUCT((Data!$B$2:$B$1214=$B3)*(Data!$J$1:$P$1=C$2)*(Data!$F$2:$F$1214=B2)*(Data!$J$2:$P$1214))

it looks like it's working, I just try to avoid sum product, I suck at reverse engineering it if I forgot what I was trying to do

 

[jasonb75]

could I index match with the J2:P1214 array of values and multiple matches?

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.

 

[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.

yeah, columns are like revenue, cost of good sold, payroll, etc - so I'm just trying to pull a single vale based on the criteria in the top row and columns B and F