Countifs criteria less than another cell (rather than an entered value)

I am trying to get a count of something less than an amount I'd like to be able to vary. When I 'hard-number' the threshold, the formula works, like this: =COUNTIF(D25:M25,"<-200")

However, I cannot get the formula to work when I use a cell reference [Cell L1] to specify the criteria. The following iterations all give the answer 0, even though one of the values in the row is -201:

=COUNTIFS(D25:M25,"<L1")
=COUNTIFS(D25:M25,"=<L1")
=COUNTIFS(D25:M25,"=<L1")

If I try to leave off the quotes:

=COUNTIFS(D25:M25,<L1)
=COUNTIFS(D25:M25,=<L1)
=COUNTIFS(D25:M25,<=L1)

I get the error message "There's a problem with this formula. Not trying to type a formula? When...."

It would be far more elegant not to have to edit the cell each time I want to try to change the threshold. I'm sure there's a simple fix, and it is just the layers of rust from not using this formula, but I'd appreciate any assistance on this one.

Jasman922

Hi @jasman922!

You could try:

PHP Code: 

=COUNTIFS(D25:M25,"<" & L1) 


Blessings!

John:

Thanks! That seems to have worked! Any good explanation of why it needs to be in this format, because nothing from Excel tells me this is the preferred (or should I say necessary) format?

Does it have to to with it being Office 365?

jasman922

Hi again, @jasman922!

The nature of the criteria argument in the COUNTIFS function is to be a string. By having a fixed part (such as the greater symbol) and a variable part (the cell where the condition is), it is simply concatenated to achieve the task in the correct way. If you put the direct reference as text, it will try to match that literal text, and not the value that is in the reference. Blessings!