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.

Paul Newcome

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?

Peggy Parchert

@Paul Newcome

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

Paul Newcome

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

Peggy Parchert

@Paul Newcome

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

Paul Newcome

Try this:

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

Peggy Parchert

@Paul Newcome

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

Paul Newcome

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

Peggy Parchert

@Paul Newcome

That worked! Thank you so much!

Peggy

Paul Newcome

Happy to help. 👍️

KimTDSYN

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".

Genevieve P.

Hey @KimTDSYN

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

KimTDSYN

Thank you. The @cell was the key.

1. 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.
2. 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.

Genevieve P.

Hi @KimTDSYN

@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.

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

@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?

KimTDSYN

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?

Genevieve P.

Hi @KimTDSYN

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