Combining IF with multiple COUNTIF statements

**smgrossie** · 03-31-2020, 02:40 PM

Hi and thank you in advance for your help!
I am a teacher trying to keep track of completion of distance learning assignments across several classes.
I need for my formula to check the number of "Complete" across a range of cells and then return "Complete" if there are 5, "None" if there are 0, and "Some" if there are 1-4.

I can nest one COUNTIF statement within the IF statement to return "None" or "Some", but I am unsure of how to alter the formula to include "Complete" if there are 5.
This is the formula I am using:
=IF(COUNTIF(C2:G2, "Complete")=0, "None", "Some")

In this case:
Complete.JPG
I need for the formula to return "Complete"

Ideas?

**Sam Capricci** · 03-31-2020, 02:51 PM

this would be one way...
=IF(COUNTIF(C20:G20,"complete")>=5,"Complete",IF(AND(COUNTIF(C20:G20,"complete")<5,COUNTIF(C20:G20,"complete")>=1),"Some",IF(COUNTIF(C20:G20,"complete")=0,"None","")))
or if you have no other options this shorter version...
=IF(COUNTIF(C20:G20,"complete")>=5,"Complete",IF(AND(COUNTIF(C20:G20,"complete")<5,COUNTIF(C20:G20,"complete")>=1),"Some","None"))

**jeffreybrown** · 03-31-2020, 02:55 PM

Hello and Welcome to the Forum,

One way

=CHOOSE(MATCH(COUNTIF($C20:$G20,"Complete"),{0,1,2,3,4,5},0),"None","Some","Some","Some","Some","Complete")

**smgrossie** · 03-31-2020, 02:57 PM

Omg thank you SO much. You're a lifesaver.

**jeffreybrown** · 03-31-2020, 02:58 PM

You are very welcome and thanks for the feedback. We are truly happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Combining IF with multiple COUNTIF statements

LinkBack

Thread Tools

Rate This Thread

Display

Combining IF with multiple COUNTIF statements

Re: Combining IF with multiple COUNTIF statements

Re: Combining IF with multiple COUNTIF statements

Re: Combining IF with multiple COUNTIF statements

Re: Combining IF with multiple COUNTIF statements

Thread Information

Users Browsing this Thread

Similar Threads

Combining Multiple IF Statements

[SOLVED] Combining Multiple If Statements

Combining mutiple IF and CountIF statements to help sort data

Excel 2007 : combining two countif statements

combining two countif statements

Combining Multiple IF Statements

Combining COUNTIF statements with different criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions