 # 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!

3 Replies

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

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")

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

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".

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

@VictoriaIwinski alternatively a single equation could be:

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