Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF using between (97; SR2)

    Is it possible to create a formula that would return values for a range between and including the end numbers. As an example:

    =SUMIF(A2:A11,">=40000#OR#<=80000",B2:B11)

    The result in this example would be 18,000

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    The following ARRAY formula should work...

    {=SUM((A2:A11<=80000)*(A2:A11>=40000)*B2:B11)}

    If unfamiliar with Array Formulas, enter starting with the "=" above and when complete press SHIFT+CTRL+ENTER to have Excel accept as an ARRAY formula. (it will add the "{}" automatically.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Thanks it works.

    What about applying it to code. I've tried:

    Var1 = Application.WorksheetFunction.Sum((Range(Cells(2, 1), Cells(11, 1)) <= 80000) * (Range(Cells(2, 1), Cells(11, 1)) >= 40000)) * Range(Cells(2, 2), Cells(11, 2))

    And receive a "runtime error 13" - type mismatch

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF using between (97; SR2)

    I don't think you can use arrays like that in VB:

    Try something like:
    <pre>Option Explicit
    Function SumArray(rng As Range)
    Dim rcell As Range
    SumArray = 0
    For Each rcell In rng
    If rcell.Value <= 80000 _
    And rcell.Value >= 40000 Then
    SumArray = SumArray + rcell.Offset(0, 1).Value
    End If
    Next
    End Function
    </pre>


    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Steve,

    I think that the array can be used in VB. If I'm able to use:

    Var2 = Application.WorksheetFunction.SumIf(Range(Cells(2, 1), Cells(11, 1)), ">=40000", Range(Cells(2, 2), Cells(11, 2)))

    with success to return the value 29,400. Then it would seem possible for Var1. Perhaps someone else can verify this.

    John

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Sorry for butting in again, but this is a non array entered formula

    =SUMPRODUCT((A1:A10>=4000)*(A1:A10<=8000),B1:B10)

    I don't know how to put this in code

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    This simlpe formula also works...

    =SUMIF(A2:A11,"<=80000",B2:B11)-SUMIF(A2:A11,"<40000";B2:B11)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF using between (97; SR2)

    Sumif is not entered as an Array formula.

    You are trying to use/enter sum as an array, I don't think THAT can be done in VB

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Thanks to all who responded but I now have a new dilemma.

    There seems to be a ghost value creeping into the function SumArray. I have attached a Zip file that contains the Excel file. If you sum the cells F5:F650 (these are for the accounts 70000 to 70170; inclusive) the result is $547,728.95. However using the function SumArray, the result is $639,175.59

    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    What can I say...
    John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF using between (97; SR2)

    The code for the SumArray function contains this:
    If rcell.Value <= 71010 _
    And rcell.Value >= 70000 Then

    So it sums for accounts 70000 through 71010, i.e. F5:F993 instead of F5:F650

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Hans,

    I'm still getting a ghost number. I've attached a modified file. Note the yellow row which depicts the variance.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SUMIF using between (97; SR2)

    John,
    I've made your function a little more generic:
    <pre>Function SumArray(rngInput As Range, varCrit1, varCrit2, rngValues As Range)
    Dim strCrit1 As String, strCrit2 As String
    strCrit1 = ">=" & varCrit1
    strCrit2 = "<=" & varCrit2
    With Application.WorksheetFunction
    SumArray = .SumIf(rngInput, strCrit1, rngValues) + _
    .SumIf(rngInput, strCrit2, rngValues) - _
    .Sum(rngValues)
    End With
    End Function
    </pre>

    so you now use it like this:
    =sumarray(E5:E42,B2,B3,G5:G42)
    where B2 and B3 contain the start and end points of your range (i.e. 70000 and 71070 in this case).
    See attached for a reworked example.
    HTH.
    PS There is an anomaly in your workbook - you show a formula as using rows 5:607 but the corresponding sum function is using 5:603 so one of the checks doesn't work.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Rory,

    What a great idea. I've modified the function slightly and got it to work on Sheet1 but it does not work on Sheet2. The cells with the new function SumArray2 are highlighted in green. I would like to refer back to the sheet using something like:

    Sheets("Sheet1").Range((Cells(2,oCol), Cells(60000,oCol))

    Where oCol = the column number of Sheet1 that I want the value from.


    Thanks for your time.,
    John

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF using between (97; SR2)

    Rory already pointed out an inconsistency in one of your formulas; there are more discrepancies between the formulas in column A and column D.
    But the cause of the "ghost" number is that you feed a far too large range to the SumArray function. The formulas in column A should look like =SumArray(E5:E12) etcetera, since you only need to sum values in column G (offset 2 from column E.)

    In your other, more recent post: why don't you use Rory's function. It'll handle formulas with references to other sheets perfectly without any modification:

    =SumArray(Sheet1!E5:E650,Sheet1!$B$2,Sheet1!$B$3,S heet1!G5:G650)

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF using between (97; SR2)

    Hans,

    In my previous post (with attachment-look at cell A30) I am using Rory's function with a slight modification to it. Within Rory's function Function SumArray(rngInput As Range, varCrit1, varCrit2, rngValues As Range) the columns associated with "rngValues " will be changing from time to time. Instead of manually changing the range each time, I thought it may be possible to do it through code ie ... Range((Cells(2,7), Cells(60000,7). I would then be able to change the "7" to the column number that I wanted information on. After creating a new function ie Function SumArray2 I did get it to work as long as the cell with the formula is in Sheet1. If I try it in Sheet2, it errors out.

    Almost there..
    John

Page 1 of 2 12 LastLast

Posting Permissions

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