Help with COUNTIFS, two criteria from the same column

Lorri Rose

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")

Protonsponge

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")

I hope that is helpful in some way,

Protonsponge

Lorri Rose

Dear Protonsponge,

Super! Thank you so much for the help!

Protonsponge

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")

I hope that is helpful in some way,

Protonsponge

Lorri Rose

thank you! that works perfectly

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?

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")

Protonsponge

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], <>"")

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))

Protonsponge

Lorri Rose

Dear Protonsponge,

Super! Thank you so much for the help!

Lorri Rose

Hiya can anyone tell me what is wrong with this formula? Each part works on it's own.

=COUNTIF([BF Coaching Framework 2015-present Qualification T]:[BF Coaching Framework 2015-present Qualification T], CONTAINS("Foil (L2)", @cell)) + COUNTIF([BF Foil Qualification Qualification Type]:[ BF Foil Qualification Qualification Type], CONTAINS ("Level 2 Foil”, @cell))