Simplifying a formula

Hi all,

I have need to count how many times someone's name appears in a particular column in an external sheet. I have the formula (provided by someone from this forum) and am using that in one of my sheets which works perfectly.

I now need to use this in another sheet, except this sheet has multiple (10+) worksheets in it. So effectively I'm having to count how many times a name appears in each worksheet. Here's the formula I'm using for it :

=SUMPRODUCT(--ISNUMBER(MATCH('X:\Folder\Joel\Temp Complaints\CCV & CBS Credits\CCV''s\CCV Weekly Results\Customer Support Temp\[Customer Support Temp - week 41.xls]Micheal Cole'!$Y$1:$Y$55, 'Week 41'!C5, 0))) + SUMPRODUCT(--ISNUMBER(MATCH('X:\Folder\Joel\Temp Complaints\CCV & CBS Credits\CCV''s\CCV Weekly Results\Customer Support Temp\[Customer Support Temp - week 41.xls]Sarah Blades'!$Y$1:$Y$75, 'Week 41'!C5, 0)))

And copying the formula over and over again until all worksheets are included in the formula... the formula looks very messy and I was wondering what I can do to simplify it?

C5 contains the name I want to match against the external sheets.

Yep, it does close and re-open at random points and we need to know the count at all times for tracking purposes.

Thanks guys, I just split the formula over many cells and hid them instead of trying to merge them into one cell... Excel was also throwing 'Formula too long' at me so I thought it best to do it this way.

Appreciate all the info though! Thanks.