# 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

Report •

#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

Report •

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

Report •

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/

Report •

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

Report •

#6
August 9, 2016 at 10:19:45
 column "AV" blank OR the month number equal to this monthWhy are you trying to match a Blank or a Number?Matching a "blank" can be problematic.To get an idea of what your sheet looks like I recreated it but I have changed the cell references by removing the A.This formula works for me:=COUNTIFS(\$O\$7:\$O\$999,"1",\$H\$7:\$H\$999,"<>Pre-Approval",\$G\$7:\$G\$999,{"<>DECL","<>WITH"},\$V\$7:\$V\$999,MONTH(Goals!\$S\$1))MIKEhttp://www.skeptic.com/

Report •

#7
August 9, 2016 at 13:58:21