Count one column by the result of another column?

[mv8167]

I am trying to count a column. My goal is for each Manual test found, I want to check to see if a date is present in another column. If a date is found, then add 1 to a counter. Same thing, I would like to check for each Manual test if a Pas or Fail was issued.

I am trying to refference a column looking for the word Manual. If the function finds the word Manual, I want it to look in another column (same row) to find the date. If there is a date, then add 1 to conut. If space is blank/empty, continue to next Manual row.

I have been trying a few solutions witrh no luck:

=IF(COUNTIF(B3:B152,"=*manual*"),COUNTIF(C3:C152,("")))

=IF(COUNTIF(B3:B152,"=*manual*"),COUNTA(C3:C152,("")))

=IF(COUNTIF(B3:B152,"=*manual*"),SUMIF(COUNTA(C3:C152,("")))

ETC...

Is it even possible to ?

 

[NBVC]

Try this instead

=SUMPRODUCT(ISNUMBER(SEARCH("manual",B3:B152))*(C3:C152<>""))

 

[Aladin Akyurek]

I am trying to count a column. My goal is for each Manual test found, I want to check to see if a date is present in another column. If a date is found, then add 1 to a counter. Same thing, I would like to check for each Manual test if a Pas or Fail was issued.

I am trying to refference a column looking for the word Manual. If the function finds the word Manual, I want it to look in another column (same row) to find the date. If there is a date, then add 1 to conut. If space is blank/empty, continue to next Manual row.

I have been trying a few solutions witrh no luck:

=IF(COUNTIF(B3:B152,"=*manual*"),COUNTIF(C3:C152,("")))

=IF(COUNTIF(B3:B152,"=*manual*"),COUNTA(C3:C152,("")))

=IF(COUNTIF(B3:B152,"=*manual*"),SUMIF(COUNTA(C3:C152,("")))

ETC...

Is it even possible to ?

=SUMPRODUCT(--ISNUMBER(SEARCH("manual",B3:B152)),--ISNUMBER(C3:C152))

 

[mv8167]

Thanks

I tried your suggestion, but the function returned 0. I should have 500.

What is "fix" for?

 

[NBVC]

I had edited that long ago.

I forgot to adjust that after I did my testing of the formula:

Use:
=SUMPRODUCT(ISNUMBER(SEARCH("manual",B3:B152))*(C3:C152<>""))

 

[mv8167]

Thanks! I dont know why I did not see that myself. I also changed C3:C152<>"" to D3:D152="*Fail*") to look for failed manual tests.

Thanks again

 

[Scott Huish]

You'll want the formula to be this then:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Manual",B3:B152))),--(ISNUMBER(SEARCH("Fail",D3:D152))))

 

[NBVC]

If *fail* is a wildcard, then you should rewrite the formula as:

=SUMPRODUCT((ISNUMBER(SEARCH("manual",B3:B152)))*(ISNUMBER(SEARCH("fail",D3:D152))))

 

[mv8167]

Thanks to you both.

Both seem to work fine. What makes your function better then the first? I guess the first function won't pickup failed. I would think it would.

Thanks twice

Mike

 

[NBVC]

Excel's "wildcard" is the Search or Find function, where you tell it the string to search for and within what cell, the * wildcard shouldn't work e.g. Search("fail",A1) or Find("fail",A1), the difference is case sensitivity only.

Did you test it to make sure, your way is actually counting when both manual and fail are in strings side by side?