Forum Discussion
Formula not working for me
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...
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))