Solved

# IF, And, Or formula

Posted on 2011-05-02
325 Views
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.
0

LVL 11

Expert Comment

0

LVL 92

Expert Comment

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

Author Comment

Sheet 3 is what I am working off of. TEST.xlsx
0

Author Comment

Mathewspatrick that works.  How can I change it to say Complete for True and Not Complete for False?
0

LVL 19

Expert Comment

that would be as simple as
``````IF([formula], "Complete", "Not Complete")
``````
0

LVL 92

Accepted Solution

Modify my formula thusly:

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

Patrick
0

## Featured Post

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.