CODEWITHSUNDEEP
excelindexingexcel-formulasumifs
Kaelo Aaj
Kaelo Aaj

Reputation: 3

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)

Upvotes: 0

Views: 118

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

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

Upvotes: 2

rachel
rachel

Reputation: 1994

For this kinds of problem, it is better to convert Month into a number first. e.g. below formula will convert Feb-01 into a date serial number that will make it easier for you to compare with other dates, such as "Jan", or "March".

DATEVALUE("Feb" & "1")

Typing below in cell G3 should work:

=LET(monthNums, DATEVALUE(B2:D2&"1"), filtered, FILTER(B3:D6,(monthNums>=DATEVALUE(G1&"1"))*(monthNums<=DATEVALUE(I1&"1")),""), BYROW(filtered,LAMBDA(row,SUM(row))))

Here is the breakdown of the formula:

Below converts months into serial numbers.

DATEVALUE(B2:D2&"1")

Then below filter out the columns with Month >= Start Month, and Month <= EndMonth

FILTER(B3:D6,(monthNums>=DATEVALUE(G1&"1"))*(monthNums<=DATEVALUE(I1&"1")),"")

Finally, below sum the filtered out columns by row:

BYROW(filtered,LAMBDA(row,SUM(row)))

Upvotes: 0

Related Questions