Need forumla to sum number of unique values in groups of different sizes

[ea2146]

Hello,

I need to find a way sum the total number of unique positions in the "status" column for each expedition (each expedition may have a different number of people in it). I have attached a sample spreadsheet with the raw data I need help with.

I have indicated my desired output in the attached sample spreadsheet.

I would be extremely grateful to anyone who can help. Thanks so much!!

[oeldere]

With an helpcolumn and an pivot table.

See the attached file.

[Kevin UK]

Hi ea2146

There have been numerous posts regarding this. Try the following in B19 and copy down. This is an array formula (ctrl+shift+enter to commit).

Please Login or Register  to view this content.

[Tony Valko]

Try this array formula** entered in C19 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$15=A19,MATCH(B$2:B$15,B$2:B$15,0)),ROW(B$2:B$15)-ROW(B$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[ea2146]

Thank you so much to each of you! I really appreciate the thorough guidance you have provided me with. Thanks again!!

[Tony Valko]

You're welcome. We appreciate the feedback!