Formulas with date values

[rowena229]

I have a column with dates and text. Is it possible to count only the cells with dates?

The formula I have is:

=SUMPRODUCT((CSS!H9:H1000="Q2")*(CSS!I9:I1000>=DATEVALUE("01/01/2000"))) -but this formula is counting all cells with a value.

Anything is appreciated. Thank you.

[Jonmo1]

It considers TEXT strings to be greater than Dates...
Add a criteria to test for numeric value..

Try
=SUMPRODUCT((CSS!H9:H1000="Q2")*(CSS!I9:I1000>=DATEVALUE("01/01/2000"))*(ISNUMBER(CSS!I9:I1000)))

[rowena229]

Thanks. But that formula is still counting all cells with a value.

[Jonmo1]

Can you post a sample book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[rowena229]

Here is sample spreadsheet of what I am working on.

[Jonmo1]

Hang on, I think I see it now...

[rowena229]

Thank you. I actually just put any date. Regardless of date I put, it is still counting the cells that contain "N/A".

[Jonmo1]

OK, the first one in G6 checking for Q1..
All the Q1's in column D have the text string "N/A" in the corresponding cell of column E.

Text strings are considered greater than dates by the > and < operators.
So indeed, there are 22 rows with Q1 in column D, and a value Greater than 1/1/2000 in Column E
Adding the ISNUMBER will resolve that issue.
=SUMPRODUCT((D5:D1000="Q1")*(E5:E1000>=DATEVALUE("01/01/2000"))*(ISNUMBER(E5:E1000)))

Now the other 3, in G7 to G9 testing Q2 Q3 and Q4
Those have real dates in the corresponding cells of column E
However, ALL of those dates are indeed greater than 1/1/2000
The dates in E are from the years 2014 or 2015
So any date in column E is in fact greater than a date from the year 2000.

[rowena229]

Wow thank you. That works!

[Jonmo1]

You're welcome.