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]

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? If so, do you mind posting your formula so I can see how you made it work?

 

[mv8167]

Yea, I tested both functions. I changing fail to failed and the count went down by one with the first function. Hence, failed is not working on the first function. The secound function still counted the records with failed in them.

One last one. To count a column without the Manual, I used =COUNTIF(B2:B152,"<>*manual*") Ok?

or, is there a better function?

Many thanks, again

Mike

 

[NBVC]

=SUMPRODUCT(--(ISNUMBER(SEARCH("manual",B2:B152))))

or

=SUMPRODUCT((ISNUMBER(SEARCH("manual",B2:B152)))*1)

 

[mv8167]

These 2 both work, of course. So does mine as it catched Passes and other variations.

Curious, what is the -- for?
=SUMPRODUCT(--(ISNUMBER(SEARCH("manual",B2:B152))))

and the *1
These 2 both work, of course. So does mine as it catched Passes and other variations.

Curious, what is the -- for?
=SUMPRODUCT((ISNUMBER(SEARCH("manual",B2:B152)))*1)

Lasttly, how do I count the reverse. I would like to count the rows that don't have manual?

Mike

 

[Aladin Akyurek]

...One last one. To count a column without the Manual, I used =COUNTIF(B2:B152,"<>*manual*") Ok?

or, is there a better function?...

That's good enough.

 

[NBVC]

Try adding the NOT function

=SUMPRODUCT(NOT(ISNUMBER(SEARCH("manual",B2:B152)))*1)

the -- and * coerce True's and False's to 1's and O's respectively so that mathematical functions can be performed on the results.

Search for Coersion or Double Unary in this forum for more information.

 

[mv8167]

Got it!

Thanks for your help.

 

[Aladin Akyurek]

Got it!

Thanks for your help.

No idea what you did get, but you shouldn't even consider exchanging

=COUNTIF(B2:B152,"<>*manual*")

against

=SUMPRODUCT(NOT(ISNUMBER(SEARCH("manual",B2:B152)))*1)

 

[mv8167]

Both seem to work fine. I am curious why one is better?

Anyway, I just tried the same function but the reverse. Now, I need the rows with no Manual but have Pass. I tried...

=SUMPRODUCT(NOT(ISNUMBER(SEARCH("Manual",B3:B152)))*1)--(ISNUMBER(SEARCH("Pass",D3:D152)))

But my result is 32 more records then I have.

Suggestions.

 

[Aladin Akyurek]

Both seem to work fine. I am curious why one is better?

Anyway, I just tried the same function but the reverse. Now, I need the rows with no Manual but have Pass. I tried...

=SUMPRODUCT(NOT(ISNUMBER(SEARCH("Manual",B3:B152)))*1)--(ISNUMBER(SEARCH("Pass",D3:D152)))

But my result is 32 more records then I have.

Suggestions.

When a COUNTIF formula does apply, it would be faster to invoke that formula than an equivalent SumProduct or any other array-grinding formulas.

For your specific 2-condition counting question invoke:

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