# SUMIFS less than criteria not working

#### iv76erson03

##### New Member
So I have a SUMIFS for a table and I want a criteria to include being less than a number in a different cell. My Formula is

=SUMIFS(Journal!\$K\$17:\$K\$10000,Journal!\$D\$17:\$D\$10000,"Retainage",Journal!\$L\$17:\$L\$10000,"<"&K2,Journal!\$F\$17:\$F\$10000,\$B14)

The bit in question is in red. It doesn't work, I'm not getting any error message or anything, it just ignores that criteria and performs the rest of the checks. The number in cell K2 on my sheet is 2. If I substitute 2 for K2 in the formula, it works. I've verified that the cell K2 does in fact contain a number and not text. Is there some bug that you can't use a greater than or less than in a SUMIFS function?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### 6StringJazzer

##### Well-known Member
I set up a file to test your formula and it works for me, so there is something going on in your workbook. No bug.

Is cell K2 is on the same sheet with the formula? How did you verify that K2 is a number? What is the format of the cell?

#### iv76erson03

##### New Member
I set up a file to test your formula and it works for me, so there is something going on in your workbook. No bug.

Is cell K2 is on the same sheet with the formula? How did you verify that K2 is a number? What is the format of the cell?

I used =ISNUMBER, also copied it to a new workbook and just entered a 2 into the box with no luck.

I should have noticed this before but in the Function Argument toolbox, I'm getting a #Value! next to that criteria, which would explain why it's just ignoring it.

#### 6StringJazzer

##### Well-known Member
Well, the formula certainly works in principle so I would say there is something about the contents of K2 that is causing the problem. I can't offering anything more without your file but I would compare your file to my file and see if you can figure out what's different.

#### iv76erson03

##### New Member
Well, the formula certainly works in principle so I would say there is something about the contents of K2 that is causing the problem. I can't offering anything more without your file but I would compare your file to my file and see if you can figure out what's different.
Been looking at it for hours, if you don't mind, I could PM you the file.

#### iv76erson03

##### New Member
Been looking at it for hours, if you don't mind, I could PM you the file.
Scratch that, I created a new sheet from scratch and now all of a sudden it's working. I still can't figure out why that cell didn't want to act like a number for a less than argument. It acted like a number for every other argument

Replies
7
Views
36
Replies
3
Views
340
Replies
8
Views
138
Replies
1
Views
333
Replies
3
Views
43

1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back