COUNTIF and AND

Barbara Witt

Hi there, I am looking to add up all customer, that have either a "no" or are blank.

I excluded yes (see below), but it does not count the blank ones, only the "no".

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], <>"yes")

What may I be doing wrong?

If I need to write a formula, which counts all the ''no" and the "blank", how do I best do this? I tried COUNTIF with OR, but I can't figure out how to do it.

=COUNTIF(OR({DAX Engagement Tracking Ambient scoped}, ="no", {DAX Engagement Tracking Ambient scoped}, ISBLANK(@row)))

Thanks so much for your help.

Best regards, Barbara

Kelly Moore

Hey Barbara

The evaluation of the OR is at the cell level, not at the range level. We say in the range of [Ambient device in scope?] the cell is OR(a, b, c, d, etc)

=COUNTIFS(Customer:Customer, @cell <> "", [Ambient device in scope?]:[Ambient device in scope?], OR(@cell <> "Yes", @cell <> ""))

If your license allows, download smartsheet's Formula template, if you haven't already. Smartsheet updates this periodically so you have keep watching the version number. Honestly, I almost always have a tab with my formula sheet open - and I've added many, many cool formulas to their original sheet. I added one I saw just this morning.

In the left, top hamburger menu choose the Solution Center. Type Formula in the search window and once found, keep clicking yes/next, ok whatever, to download into your sheets.

As always, glad to help

Kelly

Paul Newcome

@Barbara Witt My apologies for the delayed response. It looks like you were able to get it working, but to answer your question about this formula, please refer to the bold portion. You only forgot the <> argument.

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], OR(@cell = "no", @cell " "))

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], OR(@cell = "no", @cell <> " "))

Paul Newcome

Try this...

=COUNTIFS({DAX Engagement Tracking Ambient scoped}, OR(@cell = "no", @cell = ""))

Heather Duff

Hi Barbara,

I think you're close! Try this:

=COUNTIF(NOT([Ambient device in scope?]:[Ambient device in scope?], "yes"))

That should count all rows that don't have "Yes" listed in that column. Alternately, if you have more than just yes, no, and blank, and truly want to only count the "no" and blanks, here's one you could use:

=countif(OR([Ambient device in scope?]:[Ambient device in scope?]="no" , ISBLANK([Ambient device in scope?]:[Ambient device in scope?]))

Let me know if it works!

Best,

Heather

Barbara Witt

Hi @Heather D, and thanks so much for your assistance.

Unfortunately I got "incorrect argument set" on both.....Below is a screen shot. What could I be doing wrong?

=COUNTIF(NOT([Ambient device in scope?]:[Ambient device in scope?], "yes"))

=COUNTIF(OR([Ambient device in scope?]:[Ambient device in scope?], ="no", ISBLANK([Ambient device in scope?]:[Ambient device in scope?])))

Thanks for your additional assistance.

Best regards, Barbara

Barbara Witt

Hi @Paul Newcome and thanks so much for your assistance.

Unfortunately I got "unparseable".....Below is a screen shot. What could I be doing wrong?

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], OR(@cell = "no", @cell " "))

Thanks for your additional assistance.

Best regards, Barbara

Barbara Witt

Hi @KDM, would you know how to solve this one?

=COUNTIFS(NOT(ISBLANK(Customer:Customer)),[Ambient device in scope]:[Ambient device in scope], OR(@cell = "no"), ISBLANK(@cell))

I would like to count my customers (only if the field is not blank) if their Ambient device column is either empty or blank (I don't want the ones, where the column is yes).

I also tried:

=COUNTIFS(NOT(ISBLANK(@(Customer:Customer))), NOT([Ambient device in scope]:[Ambient device in scope](@cell = "yes")))

And I tried the versions as above in the email string.

What suggestions would you have?

Thanks so much for your assistance. You are a life saver :>)

Best regards, Barbara

Kelly Moore

Hey Barbara

The syntax of COUNTIFS is range1, criteria1, range2, criteria2 etc. Let's rearrange to get that syntax. Most of the time when I need to use 'Not' I prefer to use <> . It just seems simpler to me than all those parentheses. But that's just me.

=COUNTIFS(Customer:Customer, @cell<>"", [Ambient device in scope]:[Ambient device in scope], @cell<>"yes")

Is the criteria right- I was confused whether I needed to also include the not blank for the [Ambient device in scope].

Is the capitalization of 'yes' in the formula the way it will always be formatted? This formula is case sensitive. If the case can vary, we can force the formatting to all upper or all lower. Let me know if that is necessary.

Let me know if we need to tweak it further. I'm always glad to help.

cheers,

Kelly

Barbara Witt

@KDM, yes, with all these formulas I totally forgot about the syntax. I need to remember this.

We are almost there and it works, but the Ambient device in scope should be counted if blank OR <> yes.

Where do I add the OR? I have seen it in front, but is says "invalid data type".

=COUNTIFS(Customer:Customer, @cell <> "", [Ambient device in scope?]:[Ambient device in scope?], @cell <> "Yes", OR([Ambient device in scope?]:[Ambient device in scope?], @cell <> ""))

=COUNTIFS(Customer:Customer, @cell <> "", OR([Ambient device in scope?]:[Ambient device in scope?], @cell <> "Yes", [Ambient device in scope?]:[Ambient device in scope?], @cell <> ""))

Thanks so much, Barbara

Kelly Moore

Hey Barbara

The evaluation of the OR is at the cell level, not at the range level. We say in the range of [Ambient device in scope?] the cell is OR(a, b, c, d, etc)

=COUNTIFS(Customer:Customer, @cell <> "", [Ambient device in scope?]:[Ambient device in scope?], OR(@cell <> "Yes", @cell <> ""))

If your license allows, download smartsheet's Formula template, if you haven't already. Smartsheet updates this periodically so you have keep watching the version number. Honestly, I almost always have a tab with my formula sheet open - and I've added many, many cool formulas to their original sheet. I added one I saw just this morning.

In the left, top hamburger menu choose the Solution Center. Type Formula in the search window and once found, keep clicking yes/next, ok whatever, to download into your sheets.

As always, glad to help

Kelly

Barbara Witt

@KDM Yep, that fixed it - I put in @cell and its working now. Plus the OR after the range.

As always, Kelly, thanks so much.

I will download the formula spreadsheet, too. Great suggestion.

Best regards, Barbara

Paul Newcome

@Barbara Witt My apologies for the delayed response. It looks like you were able to get it working, but to answer your question about this formula, please refer to the bold portion. You only forgot the <> argument.

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], OR(@cell = "no", @cell " "))

=COUNTIF([Ambient device in scope?]:[Ambient device in scope?], OR(@cell = "no", @cell <> " "))