Formula Question

MikeSmith93

I am trying to find metrics on everything that is not multiple things.

This is a drop down column that has a list of options and I need to exclude from the count CEG 1, CEG2,...., CEG7

I attempted to use this formula =COUNTIF({ColumnReferencetoSearch}, "<>*CEG*"), but the total it gives me is incorrect (2653).

I know this is not correct because my total count for everything is 2,828 and my total for everything with CEG is 543 so the answer should be 2,285.

Any suggestions on how I can correct this formula or use a better method to calculate this data?

Thank you

Genevieve P.

Hey @MikeSmith93

Without seeing your data, my best guess is that you could use a CONTAINS Function to see if the referenced cells contain the text "CEG" and ignore those.

 =COUNTIF({ColumnReferencetoSearch}, <> CONTAINS("CEG", @cell))

Or, if that's not quite right, you could subtract the CEG total from the total overall:

 =COUNT({ColumnReferencetoSearch}) - COUNTIF({ColumnReferencetoSearch}, CONTAINS("CEG", @cell))

Let me know if either of these worked for you!

Cheers,

Genevieve

Melody Walker

I'm having the same issue. This is not giving me correct results. It's showing 4, when there are actually 18. Help?

=COUNTIFS({Org Gov Band Name}, "40 & Below", {WD Position Status}, "Unfilled", {Function}, "Infrastructure Engineering", {Status}, <>"Recruiting", {Status}, <>"Approved to Hire", {Status}, <>"Offer")

Genevieve P.

Hey @Melody Walker

Is it possible that some of your values in the source sheet have additional information, so they're not exact matches?

For example, if your {WD Position Status} cell says "Unfilled Status" or "Unfilled XXX" then these won't be counted. Or if your {Org Gov Band Name} cell says "40 and Below" versus & then it won't be counted either.

One quick way to check is to do each count individually to see which ones aren't returning all the values you want:

=COUNTIFS({Org Gov Band Name}, "40 & Below")

=COUNTIFS({WD Position Status}, "Unfilled")

=COUNTIFS({Function}, "Infrastructure Engineering")

=COUNTIFS({Status}, <>"Recruiting", {Status}, <>"Approved to Hire", {Status}, <>"Offer")

Melody Walker

I was able to get this to work by moving the parentheses to outside of the <>, and also added "--" in any blank cell in the status column.

=COUNTIFS({Org Gov Band Name}, "40 & Below", {WD Position Status}, "Unfilled", {Function}, "Infrastructure Engineering", {NtE Approved}, "Yes", {Status}, "<>Recruiting", {Status}, "<>Approved to Hire", {Status}, "<>Offer")

Genevieve P.

Hey @Melody Walker

I'm surprised to hear moving the <> inside the quotes worked! This would mean that the values in your Status column would also have <> next to them, is that correct? Or are you trying to use <> as an operator in your formula to say "does not equal"?

Melody Walker

I apologize! I was trying to say "does not equal". So: If this, this and this, but not that, that or that. Saving this one for sure!