1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Dynamic SUMPRODUCT

    From Jan Kronsell@21:1/5 to All on Tue Aug 4 01:53:19 2020
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Tue Aug 4 15:24:48 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jan Kronsell@21:1/5 to All on Tue Aug 4 14:05:26 2020
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)