# SUMIF [sum_range] question

1. ## SUMIF [sum_range] question

I have the following formula

=SUMIF(ZTM!B:B,Practice!E9,?)

Cell A1 has the following value: AA1
Cell A2 has the following value: AA9999

What I want the SUMIF formula to do is to use the values in cell A1 and A2 as the [sum_range] in the SUMIF formula. Which would ultimately look like

=SUMIF(ZTM!B:B,Practice!E9,AA1:AA9999)

I can just type in the information into the SUMIF formula because cells A1 and A2 use formulas to figure out the column reference AA1 and AA9999  Register To Reply

2. ## Re: SUMIF [sum_range] question

[Assuming XL 2002 or later....]

The sum_range can be a little misleading. The way SUMIF works is that the dimensions of range (in this case an entire column: ZTM!B:B) will determine the dimensions of sum_range. The important piece of information supplied by sum_range is the starting cell. This means that the outcome you want: ``Please Login or Register  to view this content.``
Is not strictly possible. SUMIF would conditionally sum the whole of column AA because range is the whole of ZTM!B:B.

Additionally, when you write the formula, if range and sum_range are not the same size, then the SUMIF() function becomes volatile. This means it recalculates even when a precedent cell hasn't been changed. This feature is documented here. My recommendation is that range and sum_range should always be kept the same size because it does not then imply that the function is doing something which it is not and because the function is then not volatile.

In terms of getting a cell reference from a string written into a cell: it is possible to do what you want by incorporating the INDIRECT() function into the formula. INDIRECT() is a volatile function and has a few other limitations, so it's not a very elegant solution.

Can you give us a bit more information on why you want to do this... we might be able to suggest a better approach to it?  Register To Reply

3. ## Re: SUMIF [sum_range] question

Hey Colin, thank you for your help on this issue.

Ultimately I am trying to create a formula that would answer the following question:
-For the month of X, whats is the ROI for marketing bucket Z
-To put it more concretely: For the 3d month of 2011, what is the ROI for marketing bucket SEM
-OR: For the first quarter of 2011, what is the ROI for marketing bucket SEM

So the steps I would follow are:
1) In a cell, I enter the following value "SEM"
2) In a different cell, I enter the following value "1/2011"
3) Excel takes the value SEM, goes to spreadsheet ZTM, searches for all rows that are labeled SEM Revenue and SEM Cost
4) Then, Excel would calculate (REVENUE - COST) / COST, but the values it would use for the calculation are the values that are located under the column for the month of January, 2011. So that if I needed to calculate ROI for SEM for 2/2011, I could simply just change the data in step 1 and step 2.

I hope I am being clear in my explanation.

I was going in the direction of a SUMIF formula with an OFFSET function embedded in it to limit it to only the months needed for the calculation.  Register To Reply

4. ## Re: SUMIF [sum_range] question

but realized that I was asking someone to solve the problem for me, and not actually trying to solve it myself.  Register To Reply