CODEWITHSUNDEEP
excelexcel-formula
Adrian
Adrian

Reputation: 907

dynamic column in SUMPRODUCT formula

I would like to change the last part of my formula to the more dynamic one so instead of putting letters (B, I, P, W etc.) I want my formula to sum every 7th column in range:

==SUMPRODUCT((LEFT(language!$A$3:$A$9773,8)>=TEXT($A$1,"yyyymmdd"))*(LEFT(language!$A$3:$A$9773,8)<TEXT($H$1,"yyyymmdd"))*language!$B$3:$B$9773)

The first column that formula should sum up is B, the last column is DC.

I've tried to use index formula but I receive #REF error:

=SUMPRODUCT((LEFT(language!$A$3:$A$9773,8)>=TEXT($A$1,"yyyymmdd"))*(LEFT(language!$A$3:$A$9773,8)<TEXT($H$1,"yyyymmdd"))*INDEX(language!$B$3:$B$9773,0,7))

Best Regards, Adrian

Upvotes: 0

Views: 588

Answers (1)

barry houdini
barry houdini

Reputation: 46341

Try this version, where first formula is in cell B2 (alter as required)

=SUMPRODUCT((LEFT(language!$A$3:$A$9773,8)>=TEXT($A$1,"yyyymmdd"))*(LEFT(language!$A$3:$A$9773,8)<TEXT($H$1,"yyyymmdd")),INDEX(language!$B$3:$DC$9773,0,(ROWS(B$2:B2)-1)*7+1)

Upvotes: 1

Related Questions