Andreamary
asked on
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,IFERRO R(SEARCH(" amd",B2),0 )=0,NOT(IS BLANK(D2)) ,IFERROR(F IND("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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome.
ASKER
Andrea