Sign in to join the conversation:
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.
I thought I posted this last week.
Now I understand why my "AND" didn't work.
I'm still confused about the @row because based on your explanation, the 372 below should be the count of cells that contain the "ion profile...." text but it is actually counting all cells in the "ion profile..." column that are not blank. Did I misunderstand what you wrote about when to use @row?
Hi @KimTDSYN
In this instance, you won't want the <> "" at the end which says "not blank".
This is looking to see if the Field@row is not blank, meaning that the cell to the left is not blank (which it isn't).
Instead, you'll want the {Profile} column to count the cells that have the same information as Field@row
Try deleting out the <> ""
=COUNTIFS({country}, "US", {Wave}, "1", {Profile}, Field@row)
Does that make sense?
Thanks for the quick response but it does NOT sense.
Are you saying that by removing <>"" that it should return the count of cells that have something other than nothing? I don't understand how that is the default. Regardless, if I remove the parameters to check (value after field@row) I get 0.
Let me try another way of explaining this.
Field@row simply represents the cell in this current row, in the Field column. This means we can replace it with the text that appears in that cell for the exact same formula:
=COUNTIFS({country}, "US", {Wave}, "1", {Profile}, "Ion profile data verified date")
In your previous formula, you had:
=COUNTIFS({country}, "US", {Wave}, "1", {Profile}, "Ion profile data verified date" <> "")
Saying that "these words" are not blank. While that's a correct statement, it doesn't tell the {Profile} column what to look for...so it defaults to just non-blank cells, skipping over your referenced text. Instead, you want to say "look for an exact match of these words"
{Profile} = "Ion profile data verified date"
or, in a COUNTIFS syntax:
{Profile}, "Ion profile data verified date"
With nothing after it.
If you are getting 0, this means your COUNTIFS is filtering down and cannot find any rows that match all three of your criteria at the same time. In order to get a number, this is what the formula is looking for:
{country} cell = "US" and
{Wave} cell = "1" and
{Profile} cell = "Ion profile data verified date"
all in the same row.
Is it possible that any of these criteria are spelled differently in the source sheet? For example, you're searching for the text of '1 because that number is in quotes. Do you mean to look for the actual numerical value, or for a checkbox?
{Wave} cell = 1
I'm looking for it to count how many in "profile" reference is <>"". That could be the confusion here.
I've got things working now but what further confuses me is how the below two calc give me the same result:
COUNTIFS({country}, "US", {Wave}, "1", {Profile}, Field@row<>"")
COUNTIFS({country}, "US", {Wave}, "1", {Profile}, @cell<>"")
What confuses me is the text in the "field" column is not cell text or a column header so I don't know what it's looking at. I have already changed all of the "field@row" to "@cell" but am curious what data it is looking at.
Thanks for explaining your specific requirements further! In this case, yes you're correct. You can use @cell or you can simply state <> "" since the COUNTIFS has @cell automatically implied.
COUNTIFS({country}, @cell = "US", {Wave}, @cell = "1", {Profile}, @cell <> "")
Translate it this way:
Here's more information: Create efficient formulas with @cell and @row
I'm glad the formula is working for you now!
I am currently in business trial version, but i was told it is supposed to allow for full functionality, however when I click Ctr+F my search menu is only a search bar that allows me to only find the string, and it doesn't allow for replacing. Is it me or the F&R functionality rather limited?
Hi all, I'm having some issues with an index-collect formula. I have (3) existing sheets that list a unique project name (text field), as well as a producer assigned to work that project. The producer column in all (3) sheets is set as a contact field. In a separate sheet, I'm trying to auto-populate the producer…
I looking to find unique values, in order, and separate them into columns. The "Client Update Date" column will contain dates, not in order and some dates may be duplicated. In the "1st Date Feedback I'm getting the correct date of 12/12/25 with the formula: =MIN([Client Update Date]:[Client Update Date]) In the "2nd Date…
