+ Reply to Thread
Results 1 to 11 of 11

SumIF, using a formula as the range

Hybrid View

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    16

    SumIF, using a formula as the range

    Not sure best way to describe this. I want to do a SumIF, but instead of a range I want a formula, for example...

    A B C
    1 5 5
    2 2 9
    3 8 9
    4 1 8

    Is it possible to do a SumIF where I sum the values of C only if (B-A) > 3, or something along those lines.
    The way I've been doing it is by creating an extra column and then putting column D in the range argument, but I was wondering if there was a simpler way to do it. Something like [b:b-a:a] as the range, but not sure if that's possible.

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: SumIF, using a formula as the range

    Hi annd welcome to the forum

    Try this ARRAY formula...
    =SUM(IF(B2:B5-A2:A5>3,C2:C5,0))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: SumIF, using a formula as the range

    There is also a SUMPRODUCT solution that does not require Ctrl+Shift+Enter:

    =SUMPRODUCT(--((B2:B5-A2:A5)<3),C2:C5)

  4. #4
    Registered User
    Join Date
    04-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SumIF, using a formula as the range

    Thank you both, both worked perfectly. Trying to do 1 more thing a little more complex - thought I would be able to use these formulas and adapt it but not working. Worked for the 1st part of the spreadsheet but not the second....

    The spreadsheet has data like this:
    [xxxxx] DATA
    [xxx] DATA
    [xxxxxxxx] DATA

    I want to have the range be a formula for the amount of numbers between the [ and the ]

    I've been using =FIND("[",A1)-FIND("]",A1) and just dragging that down to create a new cell, then using those cells as the range. Is there a way to incorporate that into the sumif?

  5. #5
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: SumIF, using a formula as the range

    I'm not sure I understand what you want as the conditional expression. By "amount of numbers between the [ and the ]", do you mean the value between the square brackets or do you mean the number of digits between the brackets?

    Could you restate your problem as "If this equals that, then sum these cells."

  6. #6
    Registered User
    Join Date
    04-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SumIF, using a formula as the range

    The amount of digits, restated:

    If (Find("[",A) - Find("]",A)) > 5, then Sum ColumnB

  7. #7
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: SumIF, using a formula as the range

    =SUMPRODUCT(--(LEN(A:A)>7),B:B)

    or

    Ctrl+Shift+Enter
    =SUM(IF(LEN(A:A)>7,B:B))

  8. #8
    Registered User
    Join Date
    04-02-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SumIF, using a formula as the range

    Almost got it. I wasn't clear in my previous post, the data looks like
    [xxxx] DATA <--- That's just one cell, then the numbers to be summed are in the next column. I modified the formula to almost work...

    =SUMPRODUCT(--((FIND("]",A:A)-FIND("[",A:A))=5),B:B)
    For some reason this is giving me #VALUE as the answer. When I put it for a smaller range, like only A1:A7 then it works. But if there's a blank line in that range, it goes back to #VALUE. Not sure what I'm doing wrong.

  9. #9
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: SumIF, using a formula as the range

    The error in the formula is occuring because FIND returns an error on a blank cell. You can fix this with an IF or an IFERROR, however the SUMPRODUCT formula becomes an array formula and requires CSE.

    Ctrl+Shift+Enter
    =SUMPRODUCT(--(IFERROR(FIND("]",A:A)-FIND("[",A:A)=5,0)),B:B)

    This array formula has a noticeable calculation delay when I test. This can be reduced by using cell ranges rather than whole columns, say A1:A10000 and B1:B10000.

    If you don't require a check for "[", calculation time can also be improved by doing just one FIND per cell.
    IFERROR(FIND("]",A1:A10000)=6,0)

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SumIF, using a formula as the range

    Here's another one...

    Data Range
    A
    B
    C
    1
    [xxxxx] DATA
    2
    2
    [xxx] DATA
    3
    [xxxxxxxx] DATA
    4
    5
    [xxxxx] test
    6
    ------
    ------
    ------


    This formula entered in C1:

    =SUMPRODUCT(--(FIND("]",A1:A5&"]")-FIND("[","["&A1:A5)-1=5))

    You should avoid using entire columns as range references in array formulas and the SUMPRODUCT function. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: SumIF, using a formula as the range

    you can also try it like this
    =SUMPRODUCT(--((FIND("]",A1:A17&"[]")-FIND("[",A1:A17&"[]"))=6),B1:B17)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Insert Sumif formula but range may change
    By batchy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2014, 12:36 AM
  2. Vary range in sumif formula
    By Big_Kev in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2012, 10:42 AM
  3. [SOLVED] Sumif formula and multiplying the sum range
    By finance123 in forum Excel General
    Replies: 3
    Last Post: 06-21-2012, 04:01 PM
  4. Add Formula SumIf Relative Range D = A*
    By mkerstei in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-07-2006, 04:56 PM
  5. Date Range in SumIf Formula?
    By burgeon in forum Excel General
    Replies: 2
    Last Post: 02-22-2005, 05:56 PM

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