Google Sheets: COUNTIF with dynamic criteria

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.

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))

• Or simply "="&A1 instead of CONCAT("=", A1) – user79865 Sep 7 '15 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 Sep 7 '15 at 22:03
• 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 Sep 7 '15 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 Sep 8 '15 at 0:18
• CONCAT("=", A1) was the solution I was looking for. Thanks! – Mr. B Sep 7 '16 at 2:55