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

Reply ↓  Report •


✔ Best Answer
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.

MIKE

http://www.skeptic.com/

message 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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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

Reply ↓  Report •

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

MIKE

http://www.skeptic.com/


Reply ↓  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 have
MONTH(Today())

See if you still get an error message.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

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

Reply ↓  Report •

#6
August 9, 2016 at 10:19:45
column "AV" blank OR the month number equal to this month

Why 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))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
August 9, 2016 at 13:58:21
✔ Best Answer
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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •


Ask Question