• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

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
Asked:
Andreamary
  • 2
1 Solution
 
Glenn RayExcel 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
 
AndreamaryAuthor Commented:
Perfect...thanks, Glenn!

Andrea
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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