Problem COUNTIFS when result needs to be EQUAL or SMALLER than specified number/field

=COUNTIFS(Shipped!L:L,"=<"&CY9)

from a PM...

Dibb,

What does the & do in this process? I see it used quite often but I don't use countif much myself...so I'm unfamiliar...

=COUNTIFS(Shipped!L:L,"=<"&CY9)

Thanks, Steve

the & concatanates/combines things, normaly text, so "=<"&CY9 gets turned into a criteria that the countifs() can recognise, if the reefernce is anything other than i direct reference, ie CY9, then you need to put the operators in quotes and combine then with the cell adress using &

another use for & is when you want to combine (not add) the contents of a number of cells or add tect to an asnwer
=A1&A2&A3&A4 if A1 contained John, A2 contained eats A3 contained 4 and A4 contained apples, you would get Johneats4apples
=A1&" "&A2&" "&A3&" "&A4 would give you John eats 4 apples

perhaps it would help you more if you revealed the full formula and what you are trying to do?

The formula needs to be fixed. Change it to this:

=COUNTIFS(Shipped!L:L,"<="&CY9)

The < needs to come before the =

- Moo

* Also, why are you using COUNTIFS to test for just a single condition?

@ Steve N...

if the refernce for a countif (and a few other functions) is anything other than i direct reference, ie CY9, then you need to put the operators in quotes and combine then with the cell adress using &. and seeing as, in this instance, < and =, when combined, are considered text, they need quotes around them to keep everything "legal"

@ Moo thanks for that catch =< vs<=. if i have ever done it that way before (cant recall if i have lol, but just tried it now) excel has auto-fixed it for me. and as for the use of countidS(), i figured the OP had a few other criteria to include, but again, good catch

@FD - sorry, the question about the single condition was aimed at the OP - I should have noted that.

Thanks,
Moo