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.
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?
@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
@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!
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".
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
Thank you. The @cell was the key.
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?
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:
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
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
am I able to separate this into multiple rows? in a new sheet? Desired Result -
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
