Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Sumif Formula that includes < or > sign in the criteria range cells not the actual < or > function

    Hi,

    I can't figure out how to have the sumif formula not use the greater than sign in the criteria but instead sum all the lines with >0 - 1 in it. not all the the data that is greater than 0-1... Any help would be much appreciated, I can't find any info on this. Thanks.

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If?? you are saying you want to sum .0001, .01,.099, etc then try AND
    =and(>0,<=1)

  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I am saying that Column N contains the actual text >0-1. I want to add the data in column T if it has this text. Basically I do not want the greater than or less than for the sum if. Just have the formula read the text from it.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    supply a file with before/after examples

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Balla,

    Don't know a way to do it in a formula, however, you can do it with a User Defined Function (UDF)
    Code:
    Option Explicit
    
    Function dblMySumIf(rngCriteriaRng As Range, zCriteria As String, lOffset As Long) As Double
    
        Dim rngCell As Range
        
        For Each rngCell In rngCriteriaRng
           If rngCell.Value = zCriteria Then
             dblMySumIf = dblMySumIf + rngCell.Offset(0, lOffset).Value
           End If
        Next rngCell
    
    End Function
    CustomSumIf.JPG

    Test File: VBA - Excel - UDF - CustomSumIf.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    balla506 (2014-09-18)

  7. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If I understand this correctly, how about: =SUMPRODUCT((N1:N4=">0-1")*T1:T4)

  8. #7
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    This custom function will do the trick. Thanks. I can't believe this one is not easier... but just one of the limitations I guess.

  9. #8
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Kweaver,

    I got a #value error with this formula. Thanks for the try though...may be something I am doing wrong.

Tags for this Thread

Posting Permissions

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