# Using Fromulas for cell references in the Sum() Function

1. ## Using Fromulas for cell references in the Sum() Function

I have two lookup formulas that return cell references that I'd like to use in a Sum() function, but it doesn't work.

The formulas are:
and

If I use =SUM(\$C\$822:\$C\$1246) it works and returns 12,496 which is correct.

But if I replace the cell references in the Sum() function with the formulas, it doesn't work

2. ## Re: Using Fromulas for cell references in the Sum() Function

That looks overly complicated!!

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

3. ## Re: Using Fromulas for cell references in the Sum() Function

What you are describing ("reference as result of formula") is basically the INDIRECT() function: https://support.office.com/en-us/art...1-92b6306fa261, but I agree with Glenn, that this approach feels overly complicated. Reading between the lines, it looks like you are trying to sum values between dates or after a specified date, and that is probably easier to do as a SUMIFS() function: https://support.office.com/en-us/art...6-611cebce642b

4. ## Re: Using Fromulas for cell references in the Sum() Function

You were asked to provide a sample workbook, not a picture of it. Please attach the .xlsx file.

5. ## Re: Using Fromulas for cell references in the Sum() Function

AliGW - done, thanks.

6. ## Re: Using Fromulas for cell references in the Sum() Function

Maybe missing something, but why not just use
=SUMIF(DateColumn,">="&B3,DailyGrowthTable[Deposit or Withdrawal])

7. ## Re: Using Fromulas for cell references in the Sum() Function

That's the thing I was looking for. Thanks so much for your help!

8. ## Re: Using Fromulas for cell references in the Sum() Function

You're welcome & thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)