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