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

1. ## 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. If?? you are saying you want to sum .0001, .01,.099, etc then try AND
=and(>0,<=1)

3. 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. supply a file with before/after examples

5. 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

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

balla506 (2014-09-18)

7. If I understand this correctly, how about: =SUMPRODUCT((N1:N4=">0-1")*T1:T4)

8. 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. Kweaver,

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

#### Posting Permissions

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