Excel - SUMIFS - dynamic sum range for table

0
votes

In my source data I've got a columns with data for every month and for few years.

In my working spreadsheet I want to sum the source data with 4 criteria. 3 of them are constant and one is dynamic (Month&Year). Depends on the chosen month and year I want to sum correct column in source data.

I've found a similar topic - link below: SUMIF dynamically change summing column

However, I am getting #Values error if I input a formula. I've check that all data is text and compare with Exact function as well.

Below is my formula:

=SUMIFS(INDEX(IBRACT[#All];0;MATCH(Sheet3!G$1;IBRACT[#Headers];0));IBRACT[Entity];$A$1;IBRACT[SKU];$D14)

IBRACT is the name of table.

Below is the link to screen of evaluation (in this example I wanted to sum 6th column which in the spreadsheet is column "F"). Next step of evaluation shows #Value. https://imgur.com/JHq80BM

Have anybody any idea how to solve this problem?

Best, Wiktor

1
excelexcel-formulasumifs
In your posted screenshot, the sum_range begins in row 1; all criteria_ranges begin in row 2. – XOR LX

1 Answers

0
votes

IBRACT[#All] includes the header row which is one row more than the two criteria ranges, hence the #VALUE! error. Just change the sum range to only include the data body range.

=SUMIFS(INDEX(IBRACT; 0; MATCH(Sheet3!G$1; IBRACT[#Headers]; 0)); IBRACT[Entity]; $A$1; IBRACT[SKU]; $D14)