SUMIFS less than criteria not working

iv76erson03

New Member
Joined
Mar 13, 2014
Messages
5
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 13, 2014
Messages
5
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
Joined
Jan 27, 2010
Messages
842
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 13, 2014
Messages
5
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
Joined
Mar 13, 2014
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,675
Messages
5,637,733
Members
416,981
Latest member
PLonchar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top