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


Cheers

STF

[VenatusNocte]

help resinded

[alansidman]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

[SHUTTEHFACE]

Sorry, here is the link to the other thread: https://www.mrexcel.com/forum/excel-...ml#post5136532

[FDibbins]

...I believe it is because I am counting in columns set using data validation drop down...

1. there is nothing magical about a drop down (Data Validation). It is exactly the same as if you had typed the entry in manually, the only differences being that a DD can save you some typing, help avoid typos and help restrict entry to only a specified "list". Other than that, there is basically no difference. You would refer to it in formulas etc the exact same way as if you were referencing a manually entered cell

2. I think you forgot to include the file?

3. What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue 04 Sep 2018) is actually 43347

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So you cannot use a wild card with numbers

4. If you want to just count cells with with (valid) dates in them, try a simple =count()

[SHUTTEHFACE]

1. there is nothing magical about a drop down (Data Validation). It is exactly the same as if you had typed the entry in manually, the only differences being that a DD can save you some typing, help avoid typos and help restrict entry to only a specified "list". Other than that, there is basically no difference. You would refer to it in formulas etc the exact same way as if you were referencing a manually entered cell

2. I think you forgot to include the file?

3. What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue 04 Sep 2018) is actually 43347

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So you cannot use a wild card with numbers

4. If you want to just count cells with with (valid) dates in them, try a simple =count()

Thanks!

I tried with just the "COUNT" and it is returning the correct value, 10. But the countif formulas are incorrect and unfortunately I have to use this version since there are other criteria I have to filter on.

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

Trying to upload a file the the upload keeps failing, not sure why

Cheers,

STF

[FDibbins]

Try to test the date column for >0
COUNTIFS($A$2:$A$14,">0")

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[SHUTTEHFACE]

Thanks the ">0" solution works!

[FDibbins]

Happy to help

(please dont quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.)