Get sum from unique variable

[gustavoavila]

Hello folks,

I'm trying to find a formula to find how many times people appears in a unique project. This an example data:

Table 1

Column 1	Column 2
Project 1	Jonh
Project 2	Paul
Project 1	Jonh
Project 4	Tim
Project 3	Dan
Project 2	Paul
Project 5	Tim
Project 1	Paul
Project 8	Paul
Project 7	Dan

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="6" style="width:48pt"></colgroup><tbody>
</tbody>

I'm trying to find this result:

Tim:	2	Projects
Paul:	3	Projects
Dan:	2	Projects
Jonh:	1	Projects

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Basically is search how many times Tim appears in a unique's project and summarize them.

I'm trying with sumif() in array but the results are not good.

Thanks for advice!

 

[Dave Patton]

Did you try a Pivot Table?

The formula is an Array Formula; enter it with Ctrl+Shift+Enter not just Enter.


With name criteria in C17; edit as required.

=SUM(IF(FREQUENCY(IF($B$1:$B$10=C17,MATCH($A$1:$A$10,$A$1:$A$10,0)),ROW($B$1:$B$10)-ROW(B1)+1),1))