CountIF / IsDate combination

KimTDSYN

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?

Genevieve P.

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?

KimTDSYN

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.

Genevieve P.

Hi @KimTDSYN

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

KimTDSYN

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.

Genevieve P.

Hi @KimTDSYN

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:

• @cell means "look at each individual cell in the previously stated range"
• @row means "look at the one cell in this current row"

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

I'm glad the formula is working for you now!