SOLVED

Return value if multiple DIFFERENT cells contain specific text (NOT range)

Copper Contributor

In the attached picture, I would like to create a formula that looks at different cells (not a range of cells that is continuous) to tell if all of them say 1. 

 

For example, did all the players on the R team show up on 3/16/23? If all those values are 1, then return a 1 to me. If even one of them is a zero, return a zero to me. That way, I have a quicker way to tell if everyone from that team was at our workout.

Screen Shot 2023-03-17 at 11.16.21 AM.png

4 Replies
best response confirmed by kyledupic (Copper Contributor)
Solution

@kyledupic 

=N(COUNTIF($C$2:$C$24,F8)=COUNTIFS($C$2:$C$24,F8,$D$2:$D$24,1))

You can try this formula.

presence.JPG 

hi,

 

Why not

 

=minifs($D$2:$D$24,$C$2:$C$24,F8)

 

@OliverScheurich 

@Hecatonchire 

=MIN(IF($C$2:$C$24=F8,$D$2:$D$24))

Very good solution thank you. I didn't think of this approach. Since i work with the desktop version of Excel 2013 at the moment i can't apply MINIFS but the above formula works as well if it's entered as an arrayformula with ctrl+shift+enter.

1 best response

Accepted Solutions
best response confirmed by kyledupic (Copper Contributor)
Solution

@kyledupic 

=N(COUNTIF($C$2:$C$24,F8)=COUNTIFS($C$2:$C$24,F8,$D$2:$D$24,1))

You can try this formula.

presence.JPG 

View solution in original post