Formula Question

Options

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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!