IF, And, Or formula

I am working on a spreadsheet and need some help with cause I can't seem to get it right.  The column has 1 of 3 values. C for complete, P for Partially Complete, and N for Not Started.  What I am trying to do is have a box at the bottom to tell me when cells 1 through 10 are maked C and when a cell is marked as C in cells 11 or 12 and a cell is marked as C in cell 13, 14, or 15.  I am sure this can be done I just can't seem to get the formula right.  I keep getting #VALUE for an answer.
BMCISAdminAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
BMCISAdmin,

Modify my formula thusly:

=IF(AND(COUNTIF(A1:A10,"C"),COUNTIF(A11:A12,"C"),COUNTIF(A13:A15,"C")),"Complete","Not Complete")

Patrick
0
 
David KrollCommented:
can you attach the spreadsheet?
0
 
Patrick MatthewsCommented:
Something like this should work:

=AND(COUNTIF(A1:A10,"C"),COUNTIF(A11:A12,"C"),COUNTIF(A13:A15,"C"))

That returns TRUE if all three conditions are met, and FALSE if not.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
BMCISAdminAuthor Commented:
Sheet 3 is what I am working off of. TEST.xlsx
0
 
BMCISAdminAuthor Commented:
Mathewspatrick that works.  How can I change it to say Complete for True and Not Complete for False?
0
 
Arno KosterCommented:
that would be as simple as
IF([formula], "Complete", "Not Complete")

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.