Forum Discussion

kyledupic's avatar
kyledupic
Copper Contributor
Mar 17, 2023

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

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 o...
Excel
Excel on Mac
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Mar 17, 2023

    kyledupic 

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

    You can try this formula.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor

kyledupic 

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

You can try this formula.

 

Hecatonchire's avatar
Hecatonchire
Iron Contributor
Mar 17, 2023

hi,

 

Why not

 

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

 

OliverScheurich 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 17, 2023

    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.

Resources