COUNTIFS的范围标准在不同的表 [英] COUNTIFS with range criteria on different sheets
问题描述
如果COUNTIFS功能有限制,我还没有阅读,但我遇到以下情况:
我想计算两个条件的次数是连续的。例如
Sheet 1
USERID DATE
SAM 12/1/2014
SAM 12 / 3/2014
SAM 12/4/2014
JON 12/3/2014
BOB 11/5/2014
表2
日期匹配{12/3/2014,12/4/2014,12/5/2014}
表3
用户ID DATECount
SAM 2
JON 1
BOB 0
如果所有这些信息都在同一张表中,以下公式工作(假设数据表1中列A和B列,表2中的数据位于第一行D到F,表3中的数据从D5开始。
= COUNTIFS(A:A,D5,B:B,=& $ D $ 1:$ F $ 1)
目前,为了解决这个问题,我对于条件2的范围内的每个条件都有一个SUM函数和一个COUNTIFS。但是由于有20个标准,因此它非常丑陋。 / p>
我试图找到的是一个更优雅的方式来做到这一点,或者如果有另一个功能可以返回相同的结果。
请注意,日期范围可以每个月更改。
您需要一个 SUMPRODUCT
包装器,它将通过 Sheet2!$中的单元格迭代
。 COUNTIFS
结果D $ 1:$ F $ 1
Sheet3!E5中的公式是,
= SUMPRODUCT(COUNTFS(Sheet1!A:A,D5,Sheet1!B:B,Sheet2!$ D $ 1:$ F $ 1))
根据需要填写。
I have not read if there is a limitation with COUNTIFS function, but I am experiencing the following:
I want to count the number the number of times two conditions are true in a row. For example
Sheet 1
USERID DATE
SAM 12/1/2014
SAM 12/3/2014
SAM 12/4/2014
JON 12/3/2014
BOB 11/5/2014
Sheet 2
Dates to match against {12/3/2014, 12/4/2014, 12/5/2014}
Sheet 3
USERID DATECount
SAM 2
JON 1
BOB 0
If all this information is in the same sheet the following formula works (Assuming data in Sheet 1 is in columns A and B, and the data in Sheet 2 is in the first row D to F, and the data in Sheet 3 starts at D5.
=COUNTIFS(A:A,D5,B:B,"="&$D$1:$F$1)
Currently, to get over this, I have a SUM function and a COUNTIFS for each criteria in the range for criteria 2. But it's pretty ugly since there are 20 criteria.
What I trying to find is a more elegant way to do this, or if there is another function that can return the same results.
Note that the date range can change every month.
You need a SUMPRODUCT
wrapper that will iterate the COUNTIFS
results through the cells in Sheet2!$D$1:$F$1
.
The formula in Sheet3!E5 is,
=SUMPRODUCT(COUNTIFS(Sheet1!A:A,D5,Sheet1!B:B,Sheet2!$D$1:$F$1))
Fill down as necessary.
这篇关于COUNTIFS的范围标准在不同的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!