计算与其他单元格内容匹配的范围内的单元格数 [英] Count number of cells in a range that match the content of a different cell

问题描述

我试图在Excel中使用 COUNTIF 来查找一个范围内有多少项目与单独单元格的内容匹配。想法是，范围包含每个人对给定问题的答案（一个单词的非数字答案）；比较列包含正确答案。我想计算出多少人得到了正确的答案。

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.

我尝试过： = COUNTIF（C16：BT16， = BU16）

但是似乎 COUNTIF 不允许我引用其中包含条件的单元格-它需要直接在公式中匹配条件，例如 = COUNTIF（C16：BT16， DEN）。当然，我可以这样做，但是每次评估答案时，我都必须编辑数十个公式，如果只在BU列中输入答案，则可以节省很多时间。

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.

我还尝试了 COUNTIFS ；它允许多个条件，但也不允许将条件引用到其他单元格。也搜索了几个不同的Excel网站，有什么建议吗？非常感谢！

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!

推荐答案

尝试从 = BU18 删除引号和等号-这表示您正在传递该单元格中的值，我相信这是您要查找的值。

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)

假设 BU18 包含 DEN 。您的第一个示例（ = BU18 ）说计数，如果这些单元格中的任何一个的值为 = 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 :) ).

这篇关于计算与其他单元格内容匹配的范围内的单元格数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！