# 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. 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! Sep 7 '16 at 2:55