[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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.
0
BMCISAdmin
Asked:
BMCISAdmin
1 Solution
 
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
 
BMCISAdminAuthor Commented:
Sheet 3 is what I am working off of. TEST.xlsx
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
Patrick MatthewsCommented:
BMCISAdmin,

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now