I would like to count the number of occurrences of a value in one sheet based on the value of a cell in another sheet.

I would like to use this formula:

COUNTIF(Sheet1!$A$2:$B:130, =A1)

Where A1 on this sheet holds the value that I want to look for and count in Sheet1. Of course that doesn't work. I can make it work by replacing =A1 with "=<the value that is in A1>" but there will be many COUNTIF() cells and I would rather not have to edit each to match the contents of the criterion cell.

1 Answer 1


Right after posting the question I had a flash of inspiration…

It occurred to me that maybe I could construct the comparison string if there was a function that would concatenate strings together. Sure enough, there is a function called CONCAT that will do the trick.

So, I can do what I want with:

COUNTIF(Sheet1!$A$2:$B$130, CONCAT("=", A1))

  • 2
    Or simply "="&A1 instead of CONCAT("=", A1)
    – user79865
    Commented Sep 7, 2015 at 20:06
  • Thanks! I tried that, and it didn't seem to work (gave me a result of 0 when the right answer was 4) which made me think that it was treating the ampersand as a logical AND. I'll try it again…
    – dlu
    Commented Sep 7, 2015 at 22:03
  • 2
    According to the doc support.google.com/docs/answer/3093480?hl=en when doing a numeric comparison, you can just give a value and the = is implied. (For a text comparison, = doesn't apply.) The simpler expression COUNTIF(range, A1) ought to work, assuming a cell reference evaluates to its contents. Do test that and please report back. Also beware: If the range computes floating point numbers that are very very close to A1, they won't be equal. You might need to use COUNTIFS() and test that the range's values are within some small tolerance of A1.
    – Jerry101
    Commented Sep 7, 2015 at 22:13
  • Thank you! That works (COUNTIF(range, A1)), as does "="&A1 – I'm not sure what I did wrong when I tested it the first time. I'm comparing against integers, so it's not a floating point comparison problem.
    – dlu
    Commented Sep 8, 2015 at 0:18
  • CONCAT("=", A1) was the solution I was looking for. Thanks!
    – Mr. B
    Commented Sep 7, 2016 at 2:55

Your Answer

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

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