Solved Creating an IF OR statement

June 7, 2011 at 18:47:37
Specs: Windows Vista
I am creating a open items list for a project. Each row has a column for "Open" or "Closed". The section header has the same column, using an IF-OR statement, it depends on the open items in that section. If any item is open, the section header will state "Open". If all lines are closed the header will read "Closed".

The issue I am having is that there is a variable number of rows for each section, and it is tedious to update the OR to include every new line added.

=IF(OR(G4="Open", G5="Open", G6="Open",G7= "Open",G8="Open", G9="Open", G10="Open")=TRUE, "Open", "Closed")

Is there a different way to say "If any of the cells from G4 to G10 = "Open" without having to write out each one? It would be much easier to change G10 to G13 rather than adding "G11="Open", "G12="Open","G13="Open".

Any help would be appreciated.

Thank you.


See More: Creating an IF OR statement

Report •


#1
June 7, 2011 at 19:11:25
✔ Best Answer
Try this:

If I had this:

    G
4  Open
5  Open
6  Closed
7  Open

I would use:

=IF(COUNTA(G4:G7) = COUNTIF(G4:G7,"=Closed"), "Closed", "Open")

The assumption is that all cells in G4:G7 contain data.

COUNTA counts the number of cells that contain data in the specified range. COUNTIF counts the number of cells in the specified range that contain the specified value.

When the number of cells that contain data equals the number of cells that contain "Closed", the IF statement is TRUE and will return Closed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 7, 2011 at 19:47:03
That was EXACTLY what I was looking for. Thank you!!!

Report •
Related Solutions


Ask Question