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.

 

[alansidman]

Cannot manipulate data in a picture. Please reload your data using XL2BB. See my signature for instructions

 

[derik81]

IS that necessary?

 

[Fluff]

Not sure if this will work in xl2019, but try

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

It will probably need to be confirmed with Ctrl Shift Enter.

 

[derik81]

You are a superhero!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[derik81]

You're welcome & thanks for the feedback.

I have one more doubt if you could clear it would be so helpful. What if there is only 3 sets for some dates? Like shown in the photo. Then multiplying with 0.25 won't work.

 

[Fluff]

What if there is only 3 sets for some dates? Like shown in the photo. Then multiplying with 0.25 won't work.

No it wont, but you originally said

Row 1 has the 4 same dates with Set 1 to 4 in row 2.

Which is what the formula does.

Will you have instances with only 3 dates?

 

[derik81]

No it wont, but you originally said

Which is what the formula does.

Will you have instances with only 3 dates?

Yes, I will have instances like that. Do you have a solution for such cases?

 

[Fluff]

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