17

I am trying to use COUNTIF in Excel to find how many items in a range match the contents of a separate cell. The idea is, the range contains everyone's answers to a given question (a one-word, non-numeric answer); the comparison column contains the correct answer. I want a count of how many people got the answer correct.

I tried: =COUNTIF(C16:BT16,"=BU16")

But it appears COUNTIF won't let me reference a cell with the criteria in it -- it requires the match criteria directly in the formula, such as =COUNTIF(C16:BT16,"DEN"). I can do that, of course, but I'd have to edit dozens of formulas every time I evaluate answers, and it would be much less timeconsuming if I could just enter the answers in column BU.

I also tried COUNTIFS; it allows multiple criteria, but also doesn't allow referencing the criteria to a different cell. Searched several different Excel websites too, any suggestions? Many thanks!

Gene Barrett
  • 173
  • 1
  • 1
  • 5

3 Answers3

18

Try removing the quotes and equal sign from "=BU18" - that will indicate that you are passing the value at that cell, which I believe is what you're looking for.

=COUNTIF(C16:BT16,BU18)

Assuming BU18 contains DEN. Your first example ("=BU18") is saying "count if any of these cells have a value of "=BU18", which won't match anything (unless they answered some weird questions :) ).

RocketDonkey
  • 36,383
  • 7
  • 80
  • 84
  • 1
    Exactly right. Had tried it w/o the quotes, but assumed it at least wanted an operator (?) -- ie, =, >, etc -- but this is perfect, as also answered by Rodrigo Guedes above. Thank you! – Gene Barrett Dec 20 '12 at 20:19
  • @GeneBarrett No problem, happy it helped! You're definitely right about having to use the operator in quotes at certain points (such as `COUNTIF(A:A, "<>" & B1)`, but in that case you just drop the operator in the quotes and concatenate with a reference. Good luck with everything! – RocketDonkey Dec 20 '12 at 20:21
3

Try this:

=COUNTIF(C16:BT16;BU18)

Please note that you may need to use ";" instead of "," depending on your region.

Rodrigo Guedes
  • 1,169
  • 2
  • 13
  • 27
  • Note that the semicolon is actually a regional thing - for instance it won't work on my (U.S.-based) system. – RocketDonkey Dec 20 '12 at 19:09
  • @RocketDonkey Good to know that. On my (Br-based) system it should be a semicolon. I though it was a general rule. – Rodrigo Guedes Dec 20 '12 at 19:11
  • Ha, well the only reason I know is from here actually - I would paste in sample code from other people and be baffled at how it worked with semicolons :) – RocketDonkey Dec 20 '12 at 19:15
  • 1
    That did it! Brilliant -- thanks very much. Odd, though, that nothing in the Excel documentation says so -- and I had also tried =countif(c16:bt16;=bu18) and that did NOT work -- didn't think to try it w/o the = sign. Again, outstanding, many thanks. – Gene Barrett Dec 20 '12 at 20:09
1

  • If you are using Excel 2010, you are even luckier with COUNTIFS

  • By all means you may use the search range and matching range within countif

    e.g. =COUNTIF(SEARCH RANGE, CRITERIA RANGE)

bonCodigo
  • 14,268
  • 1
  • 48
  • 91