Countifs Data validation cells not working

[SHUTTEHFACE]

Hi All,

I am trying to use a straight forward =COUNTIFS($A$2:$A$14,"<>"&" ") to count non blank date cells (i have a number of criteria but slimming down to 1 for example). I tried "*" as well but it doesn't seem to be working. I believe it is because I am counting in columns set using data validation drop down. Please see attached spreadsheet, appreciate all the help!

Dates
8/23/2018	COUNTIFS($A$2:$A$14,"*")	0
8/23/2018	COUNTIFS($A$2:$A$14,"<>"&" ")	13
8/29/2018
8/23/2018
8/23/2018
8/23/2018
8/23/2018
8/23/2018
8/29/2018
8/23/2018

<colgroup><col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:7680;width:158pt" width="210"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

STF

 

[Joe4]

A single space is not the same as a blank/empty.

The COUNT function will ignore blank entries, so you should just be able to use:

=COUNT($A$2:$A$14)

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...validation-cells-not-working.html#post4968516

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Joe4]

Cross posted https://www.excelforum.com/excel-for...ml#post4968516

I guess three minutes wasn't fast enough!

Instead of posting the same question to multiple forums at the same time, I would recommend picking one forum, posting it there, and waiting a while before posting it to other forums (give people some time to respond before moving to other forums - especially with fairly basic straightforward questions).

And as Fluff pointed out, if you Cross-Posted, please mention you are doing so and post the links to all the other forums that you have posted the question to.

 

[SHUTTEHFACE]

I guess three minutes wasn't fast enough!

Instead of posting the same question to multiple forums at the same time, I would recommend picking one forum, posting it there, and waiting a while before posting it to other forums (give people some time to respond before moving to other forums - especially with fairly basic straightforward questions).

And as Fluff pointed out, if you Cross-Posted, please mention you are doing so and post the links to all the other forums that you have posted the question to.

Thanks for the heads up,

Here is the link to the other thread: https://www.excelforum.com/excel-fo...validation-cells-not-working.html#post4968538

 

[SHUTTEHFACE]

A single space is not the same as a blank/empty.

The COUNT function will ignore blank entries, so you should just be able to use:

=COUNT($A$2:$A$14)

Thanks but I am using multiple criteria in my countifs formula, I simplified for this specific example.

Cheers,

STF

 

[Joe4]

It is generally recommended not to oversimplify your questions – otherwise, you may get answers that work on the examples you give, but not on your real situation (like we see here).
The issue is what I said in the previous post, in that a single space is not the same as blank/empty.
Try:

=COUNTIFS($A$2:$A$14,"<>" & "")

 

[SHUTTEHFACE]

It is generally recommended not to oversimplify your questions – otherwise, you may get answers that work on the examples you give, but not on your real situation (like we see here).
The issue is what I said in the previous post, in that a single space is not the same as blank/empty.
Try:

=COUNTIFS($A$2:$A$14,"<>" & "")

Thanks Joe,

Unfortunately I am getting the same result. The regular count function does result the correct value but i need to be able to add additional criteria.

Dates
8/23/2018	COUNTIFS($A$2:$A$14,"*")	0
8/23/2018	COUNTIFS($A$2:$A$14,"<>"&" ")	13
8/29/2018	COUNT(A2:A14)	10
8/23/2018
8/23/2018
8/23/2018
8/23/2018
8/23/2018
8/29/2018
8/23/2018

<colgroup><col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:7680;width:158pt" width="210"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

 

[SHUTTEHFACE]

Fixed by using ">0". thanks!

 

[Joe4]

Glad you got it to work.

If it didn't work before, then I suspect that the cells may not have really been blank.