Excel Countif with multiple if requirements

Question

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.

Answer 1

The issue you're running into is due to the fact that you can't use wildcards in that way in a SUMPRODUCT. A way that you can get around it (assuming you need to use SUMPRODUCT) is to use either SEARCH or FIND to create the necessary array for use in the SUMPRODUCT. Here is an example that has your target string in cell D1 (note that this needs to be entered as an array formula with Ctrl+Shift+Enter):

=SUMPRODUCT(
    --(Sheet1!A:A>=DATE(2012,1,1)),
    --(Sheet1!A:A<=DATE(2012,6,1)), 
    IFERROR(IF(SEARCH(D1,Sheet1!B:B)>0,1,0),0))

IFERROR accounts for the non-matches but is an Excel 2007 feature. If you're not using Excel 2007, you can try:

=SUMPRODUCT(
    --(Sheet1!A:A>=DATE(2012,1,1)),
    --(Sheet1!A:A<=DATE(2012,6,1)),
    IF(ISERROR(IF(SEARCH(D1,Sheet1!B:B)>0,1,0)),0,IF(SEARCH(D1,Sheet1!B:B)>0,1,0)))

Also, in your example formula, you'll need switch the sign to <= in the comparison to June :)

Answer 2

What version of Excel are you using? If it is 2007 or later, you can use COUNTIFS; here is a link to the syntax. You may have to use two columns, even using COUNTIFS. On Sheet2, column A should check whether Sheet1, column B contains "string" using the following formula:

=IF(ISERR(FIND("string",'Sheet1'!B1)), FALSE, TRUE)

You will need to replace 'Sheet1'!B1 with whatever cell you need, then drag it down to make it have the same number of rows as your Sheet1, column B. Then you can use COUNTIFS:

=COUNTIFS('Sheet1'!A:A, ">=" DATE(2012,1,1), 'Sheet1'!A:A, ">=" & DATE(2012,6,1), A:A, TRUE.

Answer 3

Sumproduct does not allow for wildcards. You can search for strings using ISNUMBER(SEARCH("string",range)). The formula is below.

=SUMPRODUCT(--('Sheet1'!A:A>=DATE(2012,1,1)),--('Sheet1'!A:A<=DATE(2012,6,1)),--(ISNUMBER(SEARCH("string",'Sheet1'!B:B))))

Answer 4

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*")