# Count one column by the result of another column?

#### mv8167

##### Board Regular
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 ?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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

mv8167 said:
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))

Thanks

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

What is "fix" for?

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<>""))

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

You'll want the formula to be this then:

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

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

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

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

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?

Replies
6
Views
279
Replies
3
Views
123
Replies
10
Views
155
Replies
4
Views
231
Replies
1
Views
180

1,196,360
Messages
6,014,809
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back