0

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://i.stack.imgur.com/v3s48.jpg

Have anybody any idea how to solve this problem?

Best, Wiktor

Wiktor Kopania
  • 3
  • 3
  • In your posted screenshot, the sum_range begins in row 1; all criteria_ranges begin in row 2. – XOR LX Sep 04 '18 at 12:05

1 Answers1

0

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)