Help with COUNTIFS, two criteria from the same column

Lorri Rose
Lorri Rose ✭
in Formulas and Functions

I am being dim I am sure, I need to count some information that has been imported from an excel spreadsheet which is a download from our membership database so the formating is odd.

This will be a regular download and upload so I don't want to spend time tidying things up so hive mind.

This below works fine but I also need to count if the qualification is "Level 2 Epee" how do I put both in the same forumla? I tried using OR but it didn't like this………….

=COUNTIFS([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], "Level 1 Epee, Level 2 Epee")

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭
    Answer ✓

    Hello @Lorri Rose,

    Could the following formula give you what you are looking for?

    =COUNTIF([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], "Level 1 Epee") + COUNTIF([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], "Level 2 Epee")

    image.png

    I hope that is helpful in some way,

    Protonsponge

    · Share on FacebookShare on Twitter
  • Lorri Rose
    Lorri Rose ✭
    Answer ✓

    Dear Protonsponge,

    Super! Thank you so much for the help!

    · Share on FacebookShare on Twitter

Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭
    Answer ✓

    Hello @Lorri Rose,

    Could the following formula give you what you are looking for?

    =COUNTIF([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], "Level 1 Epee") + COUNTIF([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], "Level 2 Epee")

    image.png

    I hope that is helpful in some way,

    Protonsponge

    · Share on FacebookShare on Twitter
  • Lorri Rose
    Lorri Rose ✭

    thank you! that works perfectly

    · Share on FacebookShare on Twitter
  • Lorri Rose
    Lorri Rose ✭

    Hi Protonsponge,

    another question how to select a cell that is not blank? The below doesn't work (there are 8 different qualifications and all the permutations if they have more than one so I would like an overview of just how many cells are not blank.

    =COUNTIF([Other Coaching Qualification Qualification Type]:[Other Coaching Qualification Qualification Type], IS NOT " " )

    Also If I want to pull out one qualification from that column "FIE" how would I write a formula to count only cell that have this text in them?

    · Share on FacebookShare on Twitter
  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭✭✭

    Adding a note about using OR, just in case it's helpful for anything you're working on in the future, Lorri :)

    The @cell reference can be really helpful for this sort of thing:

    =COUNT IF([BF Epee Qualification Qualification Type]:[BF Epee Qualification Qualification Type], OR(@cell="Level 1 Epee", @cell="Level 2 Epee")

    · Share on FacebookShare on Twitter
  • Protonsponge
    Protonsponge ✭✭✭✭✭✭
    edited 9:18AM

    Hello @Lorri Rose,

    Would the following work for you to count rows that are not blank. It will count the number of cells that are anything (<>) except blank ("")

    =COUNTIF([Other Coaching Qualification Qualification Type]:[Other Coaching Qualification Qualification Type], <>"")

    image.png

    For your second question, if you are looking to count the number of rows that contain "FIE", the following could be an option for you.

    =COUNTIF([Other Coaching Qualification Qualification Type]:[Other Coaching Qualification Qualification Type], CONTAINS("FIE", @cell))

    image.png

    Protonsponge

    · Share on FacebookShare on Twitter
  • Lorri Rose
    Lorri Rose ✭
    Answer ✓

    Dear Protonsponge,

    Super! Thank you so much for the help!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!