Search code examples
excelindexingexcel-formulasumifs

Excel Sumifs with dynamic columns

I am trying to find a solution to creating a solution that can dynamically adjust the number of columns in a SUMIF() formula based on a date range selected. I have a financial data set that is broken out into months and trying to create a dash that can show me what the total is for any given date range selected.

So for the image I just have Jan, Feb, and March and in this set what I am trying to do is have a formula that if start month in G1 is Jan and end month in I1 is Feb it show me the total for Jan and Feb. But if end month was Jan it would only show me Jan. Or if it was Feb and March it would just sum those two.

Any advice would be greatly appreciated!

enter image description here

I got as far creating an index formula to dynamically get a single row but am stuck there

=SUMIFS(INDEX(B$2:D$6,,MATCH(G$1,B$2:D$2,0)),A$2:A$6,F3)
Solution

  • Here is one way of doing this, it assumes that all the lables are in sorted order i.e. the one showing in range A3:A6 and the one in F3:F6

    enter image description here

    =BYROW(INDEX(B3:D6,,MATCH(G1,B2:D2,0)):INDEX(B3:D6,,MATCH(I1,B2:D2,0)),LAMBDA(x,SUM(x)))

    Or,

    =LET(x, XLOOKUP(G1,B2:D2,B3:D6):XLOOKUP(I1,B2:D2,B3:D6), MMULT(x,SEQUENCE(COLUMNS(x))^0))

    If its not aligned in the same order then:

    enter image description here

    =BYROW(F3:F6,LAMBDA(x,SUM(XLOOKUP(G1,B2:D2,B3:D6):XLOOKUP(I1,B2:D2,B3:D6)*(x=A3:A6))))

    Notes: Since as per OP you say formula are based on dates, but screenshots don't reflect that. As if those are True dates then even on custom formatting the dates will be right aligned, those are month names in text hence those are left aligned. Therefore, the formula provided here in answer should work as per the given data.

    Using ETA LAMBDA() one don't need the LAMBDA() construction as well, this is if applicableTo be noted minutely

    =BYROW(XLOOKUP(G1,B2:D2,B3:D6):XLOOKUP(I1,B2:D2,B3:D6)*(F3:F6=A3:A6),SUM)

    If none of the above formulas supports one's version of Excel then could use the following as well:

    enter image description here

    =SUMPRODUCT(
 (INDEX(B$3:D$6,,MATCH(G$1,B$2:D$2,0)):
  INDEX(B$3:D$6,,MATCH(I$1,B$2:D$2,0)))*
  (F3=A$3:A$6))

    A demo to show, formula should work, even if its dates or not dates:

    enter image description here