+ Reply to Thread
Results 1 to 4 of 4

SUMIF [sum_range] question

  1. #1
    Registered User
    Join Date
    03-23-2011
    Location
    America
    MS-Off Ver
    mac excel 2011
    Posts
    8

    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

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    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?
    Last edited by Colin Legg; 03-23-2011 at 09:19 PM.
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    03-23-2011
    Location
    America
    MS-Off Ver
    mac excel 2011
    Posts
    8

    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.

  4. #4
    Registered User
    Join Date
    03-23-2011
    Location
    America
    MS-Off Ver
    mac excel 2011
    Posts
    8

    Re: SUMIF [sum_range] question

    I posted the entire problem at this link: http://www.excelforum.com/showthread...73#post2495373

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1