计算其元素位于不同列中的配对实例 [英] Count paired instances where their elements are in different columns

问题描述

我的搜索答案受到我对相关术语缺乏认识的限制。我的目标是计算一个特定用户名出现在一列中的次数，而另一列中的数字 1 为该用户。我试图使用COUNTIF，但它似乎没有为此目的进行格式化。

My searches for answers have been limited by my lack of knowledge of the appropriate terminology. My goal is to count the number of times a specific username appears in one column AND the number 1 appears in another column for that user. I attempted to use COUNTIF, but it doesn't seem to format for that purpose.

我需要的是在列Q中出现1时，列K列出现的个人用户名数量。

What I'm needing is to count the number of instances "username" appears in column K when "1" appears in column Q.

推荐答案

如果你有= COUNTIFS（你没有提到哪个版本的Excel），请尝试在Row1中复制以适应：

If you have =COUNTIFS (you don't mention which version of Excel) please try, in Row1 and copied down to suit:

=COUNTIFS(Q:Q,1,K:K,K1)  

可能会有更好的方法（例如数据透视表，数组公式或= SUMPRODUCT），但这可能是最简单的一种。

there may be better ways (eg a PivotTable, an array formula or =SUMPRODUCT) but this could be one of the simplest.

或者，当然只是过滤并读取屏幕左下角的数值。

Alternatively of course just filter and read the count in the bottom left-hand corner of your screen.

显示COUNTIFS，SUMPRODUCT，数据透视表和过滤器选项：

Showing COUNTIFS, SUMPRODUCT, PivotTable and filter options:

在这种特殊情况下，因为您的一个条件是 1 ，您也可以使用SUMIFS。

In this special case, because one of your criteria is 1, you might also use SUMIFS.

由于数据恰好以唯一的用户列表开头，以排列Row2中的COUNTIFS版本（调整为Ctrl + Shift 加入 K2 从 K1 输入并复制到第6行将显示类似于PT中的结果。

Because the data happens to start with a list of unique Users in order the COUNTIFS version in Row2 (adjusted to K2 from K1) entered with Ctrl+Shift+Enter and copied down to Row 6 will show a result similar to that in the PT.

这篇关于计算其元素位于不同列中的配对实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！