Unable to get cell count when using line breaks char(10)

Hi Everyone,

I've been banging my head against the wall for the past two days trying to get this to work.

On sheet1 I have all the tracking data, sheet2 is resources for the spread sheet, sheet3 is the status of what the technicians have done, along with a chart. The problem lies on sheet3 when trying to count who did the work.

I've recently added VB to allow for multiple techs to be selected when doing a task. There's only three tasks, New Install, SSHD Swap, Terminal Swap. There are times when two technicians do the install and I want to make sure they get credited for doing the work. When doing the CountIFs, the result zeros out when the cell has a line feed CHAR(10). When I add the line feed in the search criteria, it's ignored.

=COUNTIFS(PALLP!$J$797:$J$800,A36,PALLP!$E$797:$E$800,"SSHD Swap")+COUNTIFS(PALLP!$J$797:$J$800,A36&CHAR(10)&"",PALLP!$E$797:$E$800,"SSHD Swap")+COUNTIFS(PALLP!$J$797:$J$800,""&CHAR(10)&A36,PALLP!$E$797:$E$800,"SSHD Swap")

(I'm using the "SSHD Swap" because it was less to type while testing)

For may sample data, I'm using Tech 31 and Tech 3

I tried this formula too, but doesn't work.

=SUM(IF(AND(A8=PALLP!$J$797:$J$800,"SSHD Swap"=PALLP!$E$797:$E$800)+IF(OR(A8&""=PALLP!$J$797:$J$800),(""&A8=PALLP!$J$797:$J$800),(""&A8&""=PALLP!$J$797:$J$800)),1,0))

Any help/input would be greatly appreciated. File attached.

John

You Rock!!! Sktneer

That was the ticket. I could have sworn I tried that, but apparently not in that context.

Marking as solved.