Dec 12 2020 09:36 AM
I am trying to get my spreadsheet to count how many zip codes that I have that are ">=" to a certain cell but "<=" another cell with the following formula and it is not working.
=COUNTIFS(G2:G302,">="&H2,G2:G302,"<"&I2)
Dec 12 2020 09:43 AM
In theory it shall work if you mean AND condition and depends on if all are texts, or all are numbers or mix.
Dec 12 2020 09:44 AM
@mehoop Could work but may depend on how the zip-codes look like. Just numbers? Including characters?
Dec 12 2020 09:48 AM
That could be 9345667 as text in one place and 9345667 as number in another - fault.
Dec 12 2020 10:14 AM
@mehoop As you are using comparison operators ">=", both the zip code and fixed values cells I2 and H2 should be numbers. If zicode is like 600-2000, Excel will treat it as text and can't validate the 'greater than or less than' equal conditions
Dec 12 2020 01:03 PM
Why not, ="600-2000">"600-1999" returns TRUE
Dec 12 2020 07:51 PM
@Sergei Baklan Because both are treated as text value. Although if you put "600-2000">"600-1999" , it returns true, it does not mean that 1st one is actually greater than 2nd. Lets take "A" and "B" and put formula in a cell as ="B">"A" . This will give True because in alphabets B comes after A. In simiar manner "600-2000">"600-1999". Its more of sorting
Dec 13 2020 05:15 AM
It's more about binary representation of data
Dec 13 2020 02:25 PM
The following
= LET(
numeric, VALUE(SUBSTITUTE(zip, "-", "")),
filteredZIP, FILTER(zip, (numeric > startN) * (numeric <= endN) ),
COUNTA(filteredZIP) )
would convert hyphenated zips to numbers and use the numeric values as a filter prior to counting.
Dec 14 2020 12:43 AM
It's still desirably to know how your zip codes looks like. Let assume they are all texts which represent numbers like in this sample
COUNTIFS() automatically transforms texts which looks like numbers into numbers. Thus in our sample in comparison text "192000" will be in background converted into the number 192000. There is the know trick to avoid that, to add CHAR(174) to such text
=COUNTIFS(F2:F6,">="&F9&CHAR(174),F2:F6,"<="&F10&CHAR(174))
or to work with SUMPRODUCT() which works as expected without surprises
=SUMPRODUCT((F2:F6>=F9)*(F2:F6<=F10))