CountIF / IsDate combination

angel.blanquet93966

Hello

I am trying to link formulas to count from a list or column all the cells that contain date and discard that which are text. But I can not get the combination of the formulas to work well.

Thank you in advance.

Find more posts tagged with

Forms

Construction

Formulas

Cell linking

Comments

Try something along the lines of

=COUNTIFS([Date Column]:[Date Column], ISDATE(@cell))

If that doesn't work, could you provide your current formulas and a little more detail?

I realize this thread is a couple years old but I can't seem to get my COUNTIFS/ISDATE formula to work.

Current formula:

=IFERROR(COUNTIFS(OR(Date1:Date1, ISDATE(Date1@row), Date2:Date2, ISDATE(Date2@row), Date3:Date3, ISDATE(Date3@row), Date4:Date4, ISDATE(Date4@row), Date5:Date5, ISDATE(Date5@row), Date6:Date6, ISDATE(Date6@row), Date7:Date7, ISDATE(Date7@row))), "")

I'm getting the #incorrect argument message. What do I have wrong?

Thanks, Peggy

@Peggy Parchert Are you able to describe exactly what you are wanting to accomplish and what your various column names are?

I want to know the count of how many of my date columns (Date1-Date7) have dates in them.

In the above example, it should show 6 in the Count column but I can't make it work.

Count is a text/number column

Date columns (Date1-Date7) is restricted to Date columns.

Thanks for the assistance.

Peggy

Try this:

=COUNTIFS([Date1]:[Date7], @cell <> "")

That gives me the count of the whole range but not by line. I probably should have said that above.

=COUNTIFS([Date1]@row:[Date7]@row, @cell <> "")

That worked! Thank you so much!

Peggy

Happy to help. 👍️

What if you want to use Isdate in a countifs to count the cells that only have a date in them?

My formula is:

=COUNTIFS({country}, "US", {Wave}, "1", {MSFT}, ISDATE(Field@row))

It keeps returning 0 yet there are 22. The whole column is of type "date".

In your COUNTIFS, you're looking to see if the Field@row is a date or not. In your case, it has text MSFT, so it's not a date.

If you're looking in the {MSFT} column in a second sheet to see if it's a date, then use @cell instead, like this:

=COUNTIFS({country}, "US", {Wave}, "1", {MSFT}, ISDATE(@cell))

Cheers,

Genevieve

Thank you. The @cell was the key.

When do you use @cell vs @row? Is the @cell if there is a formula? In one of my other cells where I am not looking for date I use: =COUNTIFS({country}, "US", {Wave}, "1", {Billing}, Field@row <> ""). In this case "@row" works.
If I were to use OR or AND with countifs do I use the @cell as well? I couldn't seem to get those to work either but I was using @row.

@row is used as a row reference within the same sheet. I like to translate the symbol as saying "in this same row".

For example, if you were to reference a cell in a column called "Assigned To", but on Row 5, you would reference it like this:

[Assigned To]5

Where the text [in these] is the column name and the number afterwards is the row number.

Screenshot 2023-07-18 at 13.52.45.png

However, when you want to reference a cell that's in the current row, instead of needing to type out the number of this row every time, you can simply use @row. That means if I want to reference the cell in the Assigned To Column of row 5, but I'm typing the formula IN row 5, I can reference it like this:

[Assigned To]@row

Screenshot 2023-07-18 at 13.52.56.png

@cell is different. This is primarily used when you're looking into an entire column but you need to tell the formula to check each cell individually, or, check @cell in the referenced range.

In your example,

=COUNTIFS({MSFT}, ISDATE(@cell))

This says, in the MSFT range listed, check each cell in that range to see if it's a date and count each one as 1.

That means for your other formula, I would have expected something like this:

=COUNTIFS({country}, "US", {Wave}, "1", {Billing}, <> "")

You could write it as:

=COUNTIFS({country}, "US", {Wave}, "1", {Billing}, @cell <> "")

Which says, check to see if the Country is "US", the Wave is "1", and the Billing is not blank (by checking each individual cell). You don't necessarily need @cell in this instance as the formula does that by default, but some functions require a range or value specified.

In regards to OR and AND, this will depend on what you're looking to do. COUNTIFS is inherently an AND already. Can you clarify exactly what you want?

Thank you Genevieve.

This is an example of how I used the AND in my countifs:

=COUNTIFS({country}, "US", {Wave}, "1", {Billing}, AND(field@row <>"", field@row<>"NA"))

It returns zero but if I use just one parameter and no "and" then it returns a value.

You mentioned that @row is used for data in the same sheet, but I have repeatedly used it to pull date from a different sheet. Per below, the "country", "wave" and "profile" are in a different file. The values in my "field" column are the column headers in that other file and the formula gives me a count of all values in the "ion profile...." column, in the other sheet, that is anything except empty. So can you clarify how I can do that if it's to be used only on the same sheet?

For your AND formula, try writing it with the {Range} listed twice with the two different criteria:

=COUNTIFS({country}, "US", {Wave}, "1", {Billing}, <> "", {Billing}, <> "NA")

There are silent @cell references in there that are defaulted in the COUNTIFS function. You don't have to type it in, but if you did it would look like this:

=COUNTIFS({country}, @cell = "US", {Wave}, @cell = "1", {Billing}, @cell <> "", {Billing}, @cell <> "NA")

Writing out this formula in plain text would go something like this:

Count all the rows that match this criteria:
The cells in the Country column say "US" and
The cells in the Wave column say "1" and
The cells in the Billing column are not blank and
The cells in the Billing column are not "NA"

In regards to your other formula, this looks at your first two criteria, and then it looks to see if the cell to the left is not blank, which it isn't, so it returns a Count. Are you sure you're getting the right count for the Profile that you're looking for?

Try typing the same formula but saying <> "" immediately after the range is listed:

=COUNTIFS({country}, "US", {Wave}, "pilot", {Profile}, <> "")

Unless what you mean is that you're looking for the Profile cell that says "Ion profile data verified date". In this instance you would reference the cell as you have, like so:

=COUNTIFS({country}, "US", {Wave}, "pilot", {Profile}, Field@row)

Here's a Help Article with more information: Create efficient formulas with @cell and @row

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…