Excel formula to dynamically create a sum range is erroring "a value used in the formula is of-CodePudding

I am trying write a formula in one cell that uses 2 other cells as the beginning and end of a range of cells from another sheet then offset 1 column and sum the values. I keep getting the error "a value used in the formula is of the wrong data type". When I step through the formulas everything seems to be correct until it gets to the final Sum() then it errors with #Value.

One attempt (this one does not have the offset one column as I never got that far before it errored):

=SUM(CELL("address",INDEX('F Mod'!AQ6:AQ15,MATCH('NC Sign'!E123,'F Mod'!AQ6:AQ15,0)))&":"&CELL("address",INDEX('F Mod'!AQ6:AQ15,MATCH('NC Sign'!E124,'F Mod'!AQ6:AQ15,0))))

Evaluates down to:

=SUM("'[CSLT Information Flow Master (version 2).xlsm]F Mod'!$AQ$6"&":"&"'[CSLT Information Flow Master (version 2).xlsm]F Mod'!$AQ$8")

Then errors.

Another attempt (has offset by altering the column in the address function):

=SUM(ADDRESS(MATCH(E123,'F Mod'!$AQ:$AQ,0),44)&":"&ADDRESS(MATCH(E124,'F Mod'!$AQ:$AQ,0),44))

Evaluates down to:

=SUM("$AR$6:$AR$8")

then errors.

It has to be something I am missing when it comes to the Sum() function but I am lost.

CodePudding user response：

Formula in B13 is:

=SUM(OFFSET(INDIRECT("G"&2 MATCH(B6;$F$3:$F$20;0));0;0;MATCH(B7;$F$3:$F$20;0)-MATCH(B6;$F$3:$F$20;0) 1;1))

Notice the INDIRECT part, where I typed INDIRECT("G"&2 MATCH That 2 comes from one row above data starts. Because data starts at row 3, then I typed 2.

CodePudding user response：

You may try in this way as well,

• Formula used in cell D4

=SUM(INDEX($H$2:$H$17,MATCH($B$5,$G$2:$G$17,0)):
INDEX($H$2:$H$17,MATCH($B$8,$G$2:$G$17,0)))

Note: Use INDEX() Function to return a reference and not a value, the use of full lookup then the colon, then another full lookup gives Excel the hint it needs that it must return a reference.

Therefore it makes a range of the two cells located and passes that to the SUM() Function wrapping around everything else.

Also, note if you are using O365, then you can use XLOOKUP() Function as well,

• Formula used in cell D10

=SUM(XLOOKUP($B$11,$G$2:$G$17,$H$2:$H$17):
XLOOKUP($B$14,$G$2:$G$17,$H$2:$H$17))

CodePudding user response：

1] Using Sum Offset function

In D2, enter formula :

=SUM(OFFSET(H1,MATCH(B5,G:G,0)-1,,RIGHT(B8,4)-RIGHT(B5,4) 1))

Or,

2] Using Sum Indirect function

In D2, enter formula :

=SUM(INDIRECT("H"&MATCH(B5,G:G,0)&":H"&MATCH(B8,G:G,0)))