SUBSTITUTE as function for criteria within COUNTIFS

Hi all,

I am having difficulty getting the following to work. =COUNTIFS($C4:$C24,(=SUBSTITUTE(D34,"","")),D4:D24,1) I have a string in a D34 which is ELECTRICIAN APPRENTICE. This is the match I want to return in the group $C4:$C24 but it won't work or if I get it to work it returns the wrong number. I thought if I could use SUBSTITUTE it would work but I can't seem to get it to work in the critera portion of the COUNTIFS function. I would appreciate any one that could help me. I have attached a copy of the worksheet

Thanks in advance!

HTML Code: 

you can use this formula in D34.

http://www.excelforum.com/members/763499.html
http://www.excelforum.com/members/698868.html


Thank you both Shukla and Dave. The trailing spaces was the killer. Since I have no control of the source data entry I think that the sumproduct and trim functions will be very helpful. I'll know more to look for that space problem in the future.

http://www.excelforum.com/members/763499.html
http://www.excelforum.com/members/698868.html


Thank you both Shukla and Dave. The trailing spaces was the killer. Since I have no control of the source data entry I think that the sumproduct and trim functions will be very helpful. I'll know more to look for that space problem in the future.