# Solved COUNTIFS using date and blank cells as criteria

August 8, 2016 at 15:56:49
Specs: Windows 7
 I am trying to get this formula to give me a count of all rows that have a value of "1" in column "AO" and column "AH" "<>Pre-Approval" or ""<>DECL" or "<>WITH" and that have column "AV" blank OR the month number equal to this month (I am using the formula =TODAY() in Goals\$S\$1) even if the data is filtered out. It works fine except for the month number part, i can't seem to figure out how to tell it to count it if column "AV" is blank or equal to this month.here is the formula I am trying to use:=COUNTIFS(\$AO\$7:\$AO\$999,"1",\$AH\$7:\$AH\$999,"<>Pre-Approval",\$G\$7:\$G\$999,"<>DECL",\$G\$7:\$G\$999,"<>WITH",\$AV\$7:\$AV\$999,MONTH(Goals!\$S\$1))

See More: COUNTIFS using date and blank cells as criteria

August 9, 2016 at 13:58:21
 Here is a SUMPRODUCT() formula that should get you what your looking for:=SUMPRODUCT(--((\$AG\$7:\$AG\$999="<>DECL")+(\$AG\$7:\$AG\$999="<>WITH")),--(\$AH\$7:\$AH\$999="<>Pre-Approval"),--(\$AO\$7:\$AO\$999=1),--(\$AV\$7:\$AV\$999=MONTH(Goals!\$S\$1))+(\$AV\$7:\$AV\$999=""))Be careful with Blank cells.Sometimes a cell LOOKS blank, but is not.MIKEmessage edited by mmcconaghy

#1
August 8, 2016 at 16:59:38
 This is untested but see if this works:=COUNTIFS(\$AO\$7:\$AO\$999,"1",\$AH\$7:\$AH\$999,"<>Pre-Approval",\$G\$7:\$G\$999,{ "<>DECL", "<>WITH" },\$AV\$7:\$AV\$999,{ "", MONTH(Goals!\$S\$1) } )You may need to wrap the whole thing in a SUM(), not sure.MIKEmessage edited by mmcconaghy

#2
August 8, 2016 at 18:25:48
 This gives me an error at the MONTH portion of the formula, I tried wrapping it in sum but got the same error

#3
August 8, 2016 at 19:28:05
 Would be helpful if we know what the error message was.MIKEhttp://www.skeptic.com/

Related Solutions

#4
August 8, 2016 at 20:15:41
 In your original post you mentioned:(I am using the formula =TODAY() in Goals\$S\$1)Try substituting TODAY() in place of Goals\$S\$1 so you would haveMONTH(Today())See if you still get an error message.MIKEhttp://www.skeptic.com/

#5
August 8, 2016 at 21:18:03
 same error, stuck at month