Forum Discussion

Carlos Reyes's avatar
Carlos Reyes
Copper Contributor
Jul 26, 2018

Help with SUM Function

Hi, I hope I get some help here in the forum. Here is my problem:

 

I have a workbook with several sheets, named with numbers from 1 to 31 representing the days of a month. I've to do a formula where I sum the value of one specific cell in a range of sheets. For example if I put the formula in the cell C9 of my master sheet and the range desired it's from sheet 14 to 20, I must sum the cell C9 from each one of the sheets in the range required. The range is defined by two cells, the first cell (A1) have the first sheet of the range (in this case 14) and the second one (A2) have the last sheet of the range (in this case 20). I tried with several formulas like this:

 

=SUM(INDIRECT(A1)&":"&INDIRECT(A2)&"!C9") this formula gets back the #REF error

=SUM("'"&INDIRECT(A1)&":"&INDIRECT(A2)&"'!C9") this too gets back the #REF error

 

=SUM(CONCATENATE(A1)&":"&CONCATENATE(A2)&"!C9")

=SUM("'"&CONCATENATE(A1)&":"&CONCATENATE(A2)&"'!C9") and this two formulas gets back a #VALUE error

 

Somebody has idea of how to make this formulas work properly? Thanks in advance for the answers

BI & Data Analysis
excel
Formulas and Functions
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 26, 2018

      Hi Lorenzo,

       

      I bit compacted your formula

      =SUM('5:8'!C9)

      but didn't catch how you suggest to make it dynamic, did I miss something?

       

      Yes, INDIRECT won't work since it shall return value from referenced cell, not the case here.

       

      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        Jul 27, 2018
        Mr. Baklan
        the compacted formula is neat!
        the query is from Mr. Reyes - I am just recommending some sort of work around.
        honestly I did not absorb fully his problem.
        maybe you can help him.
        Thank you..

Resources