Excel Countif具有多个要求 [英] Excel Countif with multiple if requirements
问题描述
= SUMPRODUCT( - ('Sheet1'!A:A> = DATE ,1,1)), - ('Sheet1'!A:A> = DATE(2012,6,1)))
我尝试使用此值作为最终值,但它是不正确的。
= SUMPRODUCT( - ('Sheet1'!A:A> = DATE(2012,1,1)), - ('Sheet1'!A:A> = DATE(2012,6,1)), - ('Sheet1'!B :B =*&string&*))
编辑:显然上面的工作,但字符串必须是EQUAL的值。 *不被识别为通配符。
谢谢。
如果您在SUMPRODUCT中使用整列,您必须具有Excel 2007或更高版本(否则不起作用),并且可以在COUNTIFS中使用通配符
= COUNTIFS('Sheet1'!A:A,> =& DATE(2012,1,1),'Sheet1'!A:A,< = & DATE(2012,6,1),'Sheet1'!B:B,* String *)
I have an Excel document that has a date column (A) and a column containing strings (B) one one sheet. On another sheet, I am doing calculations. If the cell in column A is between DATE(2012,1,1) and DATE(2012,6,1) AND the same row in column B contains "string" in any part (string) then it should count that row. Google mentioned using SUMPRODUCT but I was only able to get date between to work. Below is the SUMPRODUCT for date between.
=SUMPRODUCT(--('Sheet1'!A:A>=DATE(2012,1,1)),--('Sheet1'!A:A>=DATE(2012,6,1)))
I tried using this for the final value but it is incorrect.
=SUMPRODUCT(--('Sheet1'!A:A>=DATE(2012,1,1)),--('Sheet1'!A:A>=DATE(2012,6,1)),--('Sheet1'!B:B="*"&"string"&"*"))
EDIT: Apparently the above works, but the string must be EQUAL to the value. * is not being recognized as a wildcard.
Thanks.
If you are using whole columns in SUMPRODUCT you must have Excel 2007 or later (otherwise that doesn't work), and you can use wildcards in COUNTIFS
=COUNTIFS('Sheet1'!A:A,">="&DATE(2012,1,1),'Sheet1'!A:A,"<="&DATE(2012,6,1),'Sheet1'!B:B,"*String*")
这篇关于Excel Countif具有多个要求的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!