Countblank and If statements

Geesman

New Member
Joined
Oct 11, 2019
Messages
6
=IF(ISBLANK(Q22),"FAIL",COUNTif(C27:Q27)"=>"&$Q$22),IF(COUNTIF(C27:Q27,">"&$O$18),"FAIL","PASS")

I think I got myself twisted up here. The idea is to replace one of the Pass/Fail columns on the right with a code that leaves the portion IF(COUNTIF(C27:Q27, ">"&$O$18), "FAIL", "PASS") intact while adding an additional statement that if the required number of cycles has not been met, then the test fails.

I'd like the make the required cycles mandatory by failing the test if not filled, and I'd like to see if the number of cells filled in does not meet the required cycles, then it fails. THEN, if those conditions are met, we go to the statement above to score the test.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
=IF(OR(Q22="",COUNTA(C27:Q27)<$Q$22),"FAIL",IF(COUNTIF(C27:Q27,">"&$O$18),"FAIL","PASS"))
 

Geesman

New Member
Joined
Oct 11, 2019
Messages
6
Good morning,

Thank you for the tip. I tried that and a couple variations and I'm just getting a fail regardless of whether it fulfills or not.

This is the variation I'm working on now.

=IF(OR(Q22<>"",COUNTBLANK(C27:Q27)>$Q$22),"FAIL",IF(COUNTIF(C27:Q27,">"&$O$18),"FAIL","PASS"))

I'm going to share a link from Google Drive. The formula in Column T is the main that I'm working on which has this code, the one in Column U is just testing a the countblank formula.

https://docs.google.com/spreadsheets/d/1CNEfynkAS7i9CQDhcjZ8RXA7IGkchxQUYhob7-BpRNo/edit?usp=sharing

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
That file is locked, you need to mark it for sharing & then post the link you are given.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
That is the same link as before & it's still asking me to sign in.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Ok, the formula I supplied in post#2 works, BUT both Q22 & O18 are text values not numbers. You need to change them.
 

Forum statistics

Threads
1,082,306
Messages
5,364,410
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top