CONCATENATE function inside SUM formula

Question

I have set of numbers in D column of a sheet and I need to do SUM of set of cells and display it in different cell. My input SUM range may change each time.

For example, by using below formula, I am able to get the SUM from D3:D33 of sheet DayWise2019.

=SUM(DayWise2019!D3:D33)

Now, I am trying to use two cells to enter start and end cell number for the SUM function and trying to use concatenate to create the formula

Current sheet D13 contains first cell of SUM range Current sheet D14 contains last cell of SUM range

I am using formula like below

=SUM(CONCATENATE("DayWise2019!","D",D13,":","D",D14))

But its not working. Getting error in value "#VALUE". I checked the concatenate function alone and its working as expected.

CONCATENATE("DayWise2019!","D",D13,":","D",D14) will give DayWise2019!D3:D33

Is there anyway to resolve it ?

Unknown Unknown · Accepted Answer · 2018-09-30T09:47:14

Use INDEX to set the start row (D13) and end row (D14) cells.

=SUM(index(DayWise2019!d:d, D13):index(DayWise2019!d:d, D14))

Avoid INDIRECT.

CONCATENATE function inside SUM formula

1 Answers