Count if based on sets of data

[derik81]

Hi I am trying to get a total in column B. The total computation is as follows:
1. Row 1 has the 4 same dates with Set 1 to 4 in row 2.
2. The total is computed if there is at least 3 "Y" for a date.

For the first guy, he has 4 Y in first day and 3 Y in 3rd day so it is 2
for the second guy, he has no dates where he has at least 3 Y so 0
for the last guy, he has 4 Y in first and second day but only 2 in third day. so that total is 2.

Is there a formula that I can use in column B "total" ? Where I could check if there is at least 3 Y in a date and if yes then add 1 and so on.
If you need more explanation please let me know.
Thank you.

 

[derik81]

Is the criteria still 3 Y? Also could you have 2 sets or maybe 5 sets?

Yes, the 3 Y criteria are fixed. the number of sets could vary. Maybe on a date there will be only 1 set, which will obviously have nothing to do with 3 Y, because it is just one set. so the sets may range from 1-6, at least 3 is fixed criteria.

 

[Fluff]

Ok, try

=SUM((COUNTIFS($C$1:$N$1,$C$1:$N$1,C3:N3,"Y")>=3)/COUNTIFS($C$1:$N$1,$C$1:$N$1))

 

[derik81]

Brilliant!

 

[Fluff]

My pleasure.