Forum Discussion

VictoriaIwinski's avatar
VictoriaIwinski
Copper Contributor
Jun 29, 2021

Counting how many rows have a specific text choice, but counting it only once

Hello Community. 

 

I was wondering if there was a function in Excel that can help me with the current issue: 

 

 

Instead of counting how many "yes" responses there are in the file, I wish to know how many ROWS have at least ONE occurence of the "yes". I'm essentially trying to analyze in a quicker fashion how many individuals responded to any trauma measure/prompt. So the number of yes's aren't what is needed here. 

 

Any help would be lovely! 

excel

3 Replies

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Jun 30, 2021
    Basically applying same principle as previous answer, you can also put the helper column formula as

    =IF(COUNTIF(B2:Z2,"Yes")>0,1,0)

    and drag it down.

    Then SUM of this helper column is the desired result, i.e. rows with at least one "Yes".
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 29, 2021

    VictoriaIwinski 

    I'd use a helper column. Let's say the replies are in B2:Z500.

    In an empty column, for example column AB, enter the following formula in row 2:

     

    =COUNTIF(B2:Z2,"Yes")

     

    Fill down to row 500 (the last row with data).

    The number of rows with at least one "Yes" is

     

    =COUNTIF(AB2:AB500,">0")

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 01, 2021

      VictoriaIwinski alternatively a single equation could be:

      =LET(rng,A1:M50,SUM(--(MMULT(--(rng="yes"),SEQUENCE(COLUMNS(rng),,1,0))>0)))

Resources