Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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))
"="&A1 instead of CONCAT("=", A1)
= 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.
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.