Dynamic SUMPRODUCT
 

I have a spreadsheet with information in columns. The first row contains headers, among these names of months. Today January is in column E.

Columns A to D contains different texts. Some of these are used as criterias

Today I have a fomula like this: =SUMPRODUCT((A2:A100="Criteria 1")*(C2:C100="Criteria 2")*(D2:D100="Criteria 3")*(E2:E100))

The criteria columns will never change. It will always be A, C and D. The problem is that more columns will be added between D and E, so the column, that contains January will be changed over time. So can this column be made dynamic so it will always point to the column with "January" in row 1?

I have tried using INDEX and MATCH but I can't get it to work.

Jan K.

Dynamic SUMPRODUCT
 

Hi Jan,

Am Tue, 4 Aug 2020 01:53:19 -0700 (PDT) schriebst du in
microsoft.public.excel.worksheet.functions:

I have a spreadsheet with information in columns. The first row contains headers, among these names of months. Today January is in column E.

Columns A to D contains different texts. Some of these are used as criterias

Today I have a fomula like this: =SUMPRODUCT((A2:A100="Criteria 1")*(C2:C100="Criteria 2")*(D2:D100="Criteria 3")*(E2:E100))

The criteria columns will never change. It will always be A, C and D. The problem is that more columns will be added between D and E, so the column, that contains January will be changed over time. So can this column be made dynamic so it will always point to the column with "January" in row 1?

try:

=SUMPRODUCT((A2:A100="criteria 1")*(C2:C100="criteria 2")*(D2:D100="criteria 3")*INDEX($A$2:$Z$100,,MATCH("January",$A$1:$Z$1,0 )))



Regards
Claus B.
--
Windows10
Office 2016

Dynamic SUMPRODUCT
 

Hi Claus

=SUMPRODUCT((A2:A100="criteria 1")*(C2:C100="criteria 2")*(D2:D100="criteria 3")*INDEX($A$2:$Z$100,,MATCH("January",$A$1:$Z$1,0 )))

Thank you. Thast did the trick. I don't know what was wrong with my original formula, but it works now.

Jan K.