Countifs Data validation cells not working

S

SHUTTEHFACE

Board Regular
Joined
Aug 13, 2014
Messages
53
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!


<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>
[TD="class: xl65, width: 68"]Dates[/TD]
[TD="class: xl66, width: 210"] [/TD]
[TD="class: xl66, width: 64"] [/TD]

[TD="class: xl67, align: right"]8/23/2018
[/TD]
[TD="class: xl66"] COUNTIFS($A$2:$A$14,"*")
[/TD]
[TD="class: xl66, align: right"]0[/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] COUNTIFS($A$2:$A$14,"<>"&" ")
[/TD]
[TD="class: xl66, align: right"]13[/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/29/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/29/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl67, align: right"]8/23/2018[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

</tbody>

STF
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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:
Code: 
=COUNT($A$2:$A$14)
 
Upvote 0
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.
 
Upvote 0
Cross posted https://www.excelforum.com/excel-for...ml#post4968516
Click to expand...
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...

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
 
Upvote 0
Joe4 said:
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:
Code: 
=COUNT($A$2:$A$14)
Click to expand...

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

Cheers,

STF
 
Upvote 0
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:
Code: 
=COUNTIFS($A$2:$A$14,"<>" & "")
 
Upvote 0
Joe4 said:
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:
Code: 
=COUNTIFS($A$2:$A$14,"<>" & "")
Click to expand...

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.


<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>
[TD="class: xl63, width: 68"]Dates[/TD]
[TD="class: xl64, width: 210"] [/TD]
[TD="class: xl64, width: 64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"]COUNTIFS($A$2:$A$14,"*")[/TD]
[TD="class: xl64, align: right"]0[/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"]COUNTIFS($A$2:$A$14,"<>"&" ")[/TD]
[TD="class: xl64, align: right"]13[/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/29/2018[/TD]
[TD="class: xl64"]COUNT(A2:A14)[/TD]
[TD="class: xl64, align: right"]10[/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/29/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

[TD="class: xl65, align: right"]8/23/2018[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]

</tbody>
 
Upvote 0
Glad you got it to work.

If it didn't work before, then I suspect that the cells may not have really been blank.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Dynamic Data Validation
Replies
1
Views
65
Peter_SSs
Peter_SSs
P
  • Solved
Data Validation using Offset
Replies
1
Views
216
Akuini
Akuini
S
  • Solved
Countifs question
Replies
1
Views
383
Sarasota
S
Z
  • Solved
SORTBY Function not working as expected
Replies
7
Views
813
Fluff
Fluff
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top