I have a google sheet of 25k plus telephone records. The sheet has the phone numbers broken down so I have a single column (Column L) that has all the area codes dialed. I am trying to search that column for all the area codes from a single state which are listed in another column (Column O). I have tried a couple of COUNTIFS based on web searches but all simply return 1 as the count instead of an expected 12,689.

I tried the following: =COUNTIFS(L2:L25676,O2:O24) The first range is the range of area codes, second is range of state area codes.

I also tried: =COUNTIFS(L2:L25676,{"239","305","321","324","352","386","407","448","561","645","656","689","727","728","754","772","786","813","850","863","904","941","954"})

And this": =SUM(COUNTIFS(L2:L25676,{"239","305","321","324","352","386","407","448","561","645","656","689","727","728","754","772","786","813","850","863","904","941","954"}))

Any and all advise is greatly appreciated. I am certain at this point I am misusing the COUNTIFS function.

  • Welcome! Please try =ArrayFormula(SUM(COUNTIF(L2:L,O2:O)))
    – JohnSUN
    Sep 13, 2022 at 16:15

1 Answer 1


Try the filter(match()) pattern, like this:

=counta( iferror( filter(L2:L, match(L2:L, O2:O, 0)) ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.