Sum Array Formula and Wildcards

[EliseFreedman]

Hi There

I am in the process of building a team tracker for our team at work. I have a scorecard which has all the actions and the actionees. I am then trying to build a summary sheet which has all the members of our team and the number of actions that are overdue, on track or their due date is coming up for each project that we have. I am using a sum array formula to caculate this which is as follows
=SUM((Cardinus!$F$20:$F$29="E Freedman")*(Cardinus!$K$20:$K$29="In Progress"))+SUM((Cardinus!$F$4:$F$8="E Freedman")*(Cardinus!$K$4:$K$8="In Progress"))

This works fine provided that there is only one actionee e.g E Freedman. The problem arises when there is more than one actionee. For Example P Plowright E Freedman. How do I amend my formula so that actions like that will be picked up. Can I use a wildcard or do I need to completely change my formula

 

[EliseFreedman]

Ive managed to figure it out

 

[SkipVought]

hi,

I'd use...
[tt]
SUMPRODUCT((RIGHT($F$20:$F$29,LEN("E Freedman"))="E Freedman")*($K$20:$K$29="In Progress"))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}