Help w/counting cells with blanks with OR statement

jmo

Team - I have a sheet that has 5 columns that are used for answering specific questions:

I also have other columns that are used to verify information:

I need a formula that will return a count if any of the 1-5 questions are blank OR the Cyber Intel Verified is not checked OR the Cyber Intel - follow up required is blank.

Any suggestions?

Sam M.

Hi Jmoser,

 

I tried something and it worked, but it might not be the most optimal but it might help.

This is what I was testing:

To simulate something that is not blank I filled it up with a 1.

 

One way:

 

On the “One or more Blank” column there is this:

=IF(OR(ISBLANK([Question 1]@row), ISBLANK([Question 2]@row), ISBLANK([Question 3]@row), ISBLANK([Question 4]@row), ISBLANK([Question 5]@row), [Cyber intel verified]@row = 0, ISBLANK([Cyber intel follow up]@row)), "Yes", "No")

 

It checks if something is blank from question 1 to cyber intel follow up. I don’t know where you are going to use the count, but from here you can see if something on the row is blank and use the “Yes” or “No” on your next formula.

 

 

Another way:

Another way to see if something is blank can be seen on column “How many are missing”.

The formula I used there is:

 

=COUNTIF([Question 1]@row:[Cyber intel follow up]@row, ISBLANK(@cell)) + IF([Cyber intel verified]@row = 0, 1)

It checks the row cell by cell seeing if something is blank and counts how many are blank, and also the checkbox didn’t seem to react to isblank so I added an IF() to add a 1 if it was unchecked. And from here it might help you form your count formula that you mentioned.

 

Hope it helps,

Samantha

jmo

Hi @Samantha Manzanera - will this work on a summary sheet or do I have to use that on the source data sheet?

Sam M.

I did some more testing and it seems it might work, but you might have to specify the row:

You can also hide the column on the sheet and lock it and still use the data calculated.

Hope it helps,

jmo

Hi @Samantha Manzanera - I created a new column call 1 or more Qs blank and tried to insert your example, mine came out like this (just to see if it would count blanks for the first 5 Qs:

=IF(OR(ISBLANK([1. Aware of MS vulnerability?]@row), ISBLANK([2. Use of any affected on-prem listed MS Exc Svr?]@row), ISBLANK([3. Which MS Exchange Server version(s)?]@row), ISBLANK([4. Describe DHS action steps taken]@row), [5. Have malicious actors accessed your network?]@row)), "Yes", "No")

I keep getting an UNPARSEABLE error.

I haven't tried to pull in the other columns ([Cyber intel verified] or [Cyber intel follow up]) since I cannot seem to get the string right in the first part.

Sam M.

Hi @jmoser,

From your formula:

=IF(OR(ISBLANK([1. Aware of MS vulnerability?]@row), ISBLANK([2. Use of any affected on-prem listed MS Exc Svr?]@row), ISBLANK([3. Which MS Exchange Server version(s)?]@row), ISBLANK([4. Describe DHS action steps taken]@row), [5. Have malicious actors accessed your network?]@row)), "Yes", "No")

Maybe the error is there:

you have

[5. Have malicious actors accessed your network?]@row )

You are missing an ISBLANK

maybe you can try:

=IF(OR(ISBLANK([1. Aware of MS vulnerability?]@row), ISBLANK([2. Use of any affected on-prem listed MS Exc Svr?]@row), ISBLANK([3. Which MS Exchange Server version(s)?]@row), ISBLANK([4. Describe DHS action steps taken]@row), ISBLANK([5. Have malicious actors accessed your network?]@row)), "Yes", "No")

It might be that, if not please let me know