Countifs and <>

Options

Sorry for the simple question, but I'm braindead.

From a reference sheet (See 2n photo, I need to count # of Agents in first sheet photo, if Year is =Year@row and Agent Status is <> "Not in Agent Count". For some reason, I keep getting 0. Apprecaite your help!

Sheet Referencing:

Sheet with formula in Agent count column:


If tried this: =COUNTIFS({Agent}, {Goal Year}, Year@row, {Status}, <> "Not in Agent Count") which give me Incorrect argument

And this just to get a count:

=COUNTIFS({Goal Year}, Year@row, {Status}, <>"Not in Agent Count") Which gives me 0

Best Answer

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    Answer ✓
    Options

    This is doable, but are we sure there is no other way to make this count without adding helper columns? Is it possible to count a column that has blank cells and not one condition? Or is it possible to count blank + other conditions?

Answers

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    Options

    I think part of the problem is that the Agent Status could be blank, because if I change my formula to count Goal Year if it's @row and "", it counts them. However, this column might be blank and it might say something else. I need it to count blank and other status unless it says "Not in Agent Count". Any thoughts on this?

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    edited 12/10/21
    Options

    Hi @Mike matthys,

    Thanks for your reply, but it looks blank. Was there something for me to try?

    I can tell you that =COUNTIFS({Goal Year}, Year@row, {Status}, <>"Agent Not in Count"), does not count the rows where Status is blank. How can I resolve that?

  • Mike matthys
    Options

    @Andrea Westrich

    Hmmm thats weird, ok here is what you can do as workaround :)

    add a text/number column as helper and put this code in and make it a column formula

    =IF([Agent status]@row <> ""; 1; 0)


    on your reference sheet do your count on that column

    {Status} is link to field above

    use this if you want to see where the requested field is empty

    =COUNTIFS({Status}; 0; {Goal Year}; Year@row)

    and this to see if its filled with "Not in Agent Count"

    =COUNTIFS({Status}; 1; {Goal Year}; Year@row)


    hope it helps you out :)

  • Mike matthys
    Options

    change this code i gave

    =IF([Agent status]@row <> ""; 1; 0)

    to

    =IF([Agent status]@row <> "Not in Agent Count"; 0; 1)


    i didnt see your second post, this will say 0 to empty en anything else then "Not in agent count" and 1 to "not in agent count"

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    Answer ✓
    Options

    This is doable, but are we sure there is no other way to make this count without adding helper columns? Is it possible to count a column that has blank cells and not one condition? Or is it possible to count blank + other conditions?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!