Hope someone can help with this. I want to count distinct/unique names (strings) in a coloumn, but I also need to account for which team the person is in. My data looks a little like this (see below), so in this example I'd like to return that there are 3 people in each team and rule out the duplicates.

I've been playing with CPearsons formula and come up with this, but I can't figure out the first part of the formula where I add the criteria for the team. The sumproduct is just trial and error.

=SUMPRODUCT(--('DQ DATA'!$E:$E=D11))*(SUM(IF(FREQUENCY(IF(LEN('DQ DATA'!A:A)>0,MATCH('DQ DATA'!A:A,'DQ DATA'!A:A,0),""), IF(LEN('DQ DATA'!A:A)>0,MATCH('DQ DATA'!A:A,'DQ DATA'!A:A,0),""))>0,1)))

Thanks for any help.

Team | Name |

Team A | Ken |

Team A | Ken |

Team A | Ken |

Team A | Bob |

Team A | Jim |

Team B | Kate |

Team B | Sarah |

Team B | Andy |

Team B | Sarah |

<tbody>

</tbody>