• Status: Solved
• Priority: Medium
• Security: Public
• Views: 62

# Counting # of WOs in Excel meeting specific criteria (updated)

I realized in implementing a solution yesterday that I need a tweak.

The current formula is showing Column F (COUNT_WO) = 1 when Column D is not blank and Column E is blank. I need the formula to be revised so that Column F (COUNT_WO) = 0 if Column D is not blank and Column E is blank.

I am resubmitting my sample spreadsheet that uses the current formula as outlined below, and I've added a column called 'Desired Results' to show what I am after.

The current formula being used in the sample spreadsheet in F2 is:
=IF(AND(COUNTIF(\$A\$2:A2,A2)=1,IFERROR(SEARCH("amd",B2),0)=0,NOT(ISBLANK(D2)),IFERROR(FIND("N/A",E2),0)=0),1,0)

SUMMARY OF CRITERIA
The criteria to be met:
Col A is not blank, and the number is unique (format of number is always "yyyy-#######")
Col B does not contain the text string "amd" or "AMD"
Col D is not blank
Col E is not blank, and does not contain "N/A"

Then:
Column F (COUNT_WO) = 1

If the above conditions are not met, then:
Column F (COUNT_WO) = 0

If Col D is not blank and Col E is blank, then:
Column F (COUNT_WO) = blank

If error, then:
Column F (COUNT_WO) = blank
EE_Summary_Count_WOs_Updated.xlsx
0
Andreamary
• 2
1 Solution

Excel VBA DeveloperCommented:
Here is the revised formula.  It only needed an extra test inside the AND(...) function to see if the value in column E is not blank.
=IF(AND(NOT(ISBLANK(A2)),COUNTIF(\$A\$2:A2,A2)=1,IFERROR(SEARCH("amd",B2),0)=0,NOT(ISBLANK(D2)),NOT(ISBLANK(E2)),IFERROR(FIND("N/A",E2),0)=0),1,0)

I also added a test to check if the value in column A is also not blank; that was left out of the previous formula.

-Glenn
0

Author Commented:
Perfect...thanks, Glenn!

Andrea
0

Excel VBA DeveloperCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.