SUMIFS less than criteria not working

[iv76erson03]

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?

Thanks in advance!

 

[6StringJazzer]

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.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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]

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.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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, 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]

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]

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