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!
For my Customer ID column, I'd like to count how many cells have errors so I'm trying this: =COUNTIF([Customer ID]:[Customer ID], not(iserror(@row ))) The Customer ID column is a lookup from elsewhere, and typically the error that could show up is #NO MATCH. I've tried a few variations of this formula but can't get it to…
Hello! I am trying to use sheets in a resource management type of way without the complexity. I have two sheets I am using. Sheet One: Holds all the resources. It includes allocations based on dates. Sheet Two: Displays summary of allocation. I would like sheet two to update the Total Percent Allocation for each person…
Hello, I am trying to create a formula that will separate text. I can't use the Left or Right and the length of names is different. Example: I need to return the first name only in a different column. Format is LastName, First Name Full Name First Name Smith, Joe Joe Jones, Ralph Thompson, Leah Robbins, Sam Robinson, Sally…
