Countifs and <>

Andrea Westrich

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

Andrea Westrich

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?

Andrea Westrich

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

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

@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

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

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?