Sumifs / index / match in another sheet

**lofgren** · 09-11-2019, 08:47 AM

Hello all,

I need a little help with a Sumifs + Index + Match formula, in order to do this:
- look for "ABCDEFGH" (Sheet2!A7) and "WEEK 37 2019" (SHEET2!G3) in sheet1 and make sum between the values in Sheet1 column F - for week 37. (see attachment)

If you have any free time in order to help, would be great.
Thank you in advance.

**AliGW** · 09-11-2019, 08:53 AM

Try this:

=SUMPRODUCT((Table1[KW]=G3)*(Table1[[#Headers],[Column1]:[Column4]]=A7),Table1[[Column1]:[Column4]])

For a European locale:

=SUMPRODUCT((Table1[KW]=G3)*(Table1[[#Headers];[Column1]:[Column4]]=A7);Table1[[Column1]:[Column4]])

**PaulM100** · 09-11-2019, 08:53 AM

Try:=SUMPRODUCT((Table1[KW]=Sheet2!$G$3)*(Table1[[#Headers],[Column1]:[Column4]]=Sheet2!$A$7),Table1[[Column1]:[Column4]])

**lofgren** · 09-11-2019, 11:41 AM

Thank you both for your answers.
Both are working just fine on my test file, but on both i receive an error when trying to paste it into my working file.

It seems that the problem is here: (Table1[[#Headers]
Any ideas?

**PaulM100** · 09-11-2019, 11:50 AM

Table1[[#Headers] is the headers range, the range that contains the week no. Select it again

**lofgren** · 09-11-2019, 02:43 PM

Already did it, same result.
I don't really understand the formula. Anybody could explain me, this part please?
[Column1]:[Column4]

My table's name (on working workbook) is not Table1, but Table2. I changed Table1 to Table2 in formula, but it seems that A7 is not found on Table2.
Attached you can see how the formula looks after i made all the changes

Edit: I just relised what [Column1]:[Column4] means