0

Thanks for your attention:

I have an ultra-marathon scenario with multiple contestants each competing in multiple events (i.e. swim, cycle, run).

I need a little help on the formula to only count the participants when they have completed all three activities.

I have tried DCOUNT& ARRAY formulas without success (wondering if there is a non-VBA solution?)

.

Column A = names, Column B = activities

[ Andy - Swim ]

[ Andy - Run ]

[ Andy - Cycle ]

[ Brent - Swim ]

[ Brent - Run ]

[ Brent - Cycle ]

.

Many Thanks, Stu.

Stuart
  • 1
  • 3
  • I would suggest structuring your data. For instance, have column A house the names and have each column to the right (B, C, D, etc) house one particular activity. This task becomes very simple then. – Kyle Mar 09 '15 at 16:19

1 Answers1

0

Value in C4, assuming data starts on row 2, is sorted by name, paste all the way down:

=if(AND(A2=A3=A4),1,0)

Then, a sum or countif function at the bottom of the column.

bf2020
  • 742
  • 4
  • 7