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 ?

 

[mv8167]

Aladin,

Thanks, that worked fine.

Last one, if I want to count the tests that are not Manual, or empty lines, would I use:

=SUMPRODUCT(1-ISNUMBER(SEARCH("Manual",B3:B152)),1-ISNUMBER(SEARCH("",B3:B152)))

 

[shodan]

Aladin,

I was trying to evaluate your formula:

Aladins Formula:

=SUMPRODUCT(--ISNUMBER(SEARCH("manual";A1:A10));--ISNUMBER(B1:B10))

Can you please confirm:
*do you use the isnumber to convert error values resulting from the search function into Falses?
*then with -- you get them back to 0 and 1's/

trying to learn here. thanks for teaching.[/b]

 

[Aladin Akyurek]

Aladin,

I was trying to evaluate your formula:

Aladins Formula:

=SUMPRODUCT(--ISNUMBER(SEARCH("manual";A1:A10));--ISNUMBER(B1:B10))

Can you please confirm:
*do you use the isnumber to convert error values resulting from the search function into Falses?

Yes.

*then with -- you get them back to 0 and 1's/

Yes.

 

[Aladin Akyurek]

Aladin,

Thanks, that worked fine.

Last one, if I want to count the tests that are not Manual, or empty lines, would I use:

=SUMPRODUCT(1-ISNUMBER(SEARCH("Manual",B3:B152)),1-ISNUMBER(SEARCH("",B3:B152)))

Either:

=SUMPRODUCT(1-ISNUMBER(SEARCH("Manual",B3:B152)),--(B3:B152<>""))

Or:

=SUMPRODUCT(1-ISNUMBER(SEARCH("Manual",B3:B152)),1-ISBLANK(B3:B152))