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

%3CLINGO-SUB%20id%3D%22lingo-sub-2499130%22%20slang%3D%22en-US%22%3ECounting%20how%20many%20rows%20have%20a%20specific%20text%20choice%2C%20but%20counting%20it%20only%20once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2499130%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20there%20was%20a%20function%20in%20Excel%20that%20can%20help%20me%20with%20the%20current%20issue%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22VictoriaIwinski_0-1625004221269.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292433i6CC869B645090651%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22VictoriaIwinski_0-1625004221269.png%22%20alt%3D%22VictoriaIwinski_0-1625004221269.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20counting%20how%20many%20%22yes%22%20responses%20there%20are%20in%20the%20file%2C%20I%20wish%20to%20know%20how%20many%20ROWS%20have%20at%20least%20ONE%20occurence%20of%20the%20%22yes%22.%20I'm%20essentially%20trying%20to%20analyze%20in%20a%20quicker%20fashion%20how%20many%20individuals%20responded%20to%20any%20trauma%20measure%2Fprompt.%20So%20the%20number%20of%20yes's%20aren't%20what%20is%20needed%20here.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20lovely!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2499130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2499184%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20how%20many%20rows%20have%20a%20specific%20text%20choice%2C%20but%20counting%20it%20only%20once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2499184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1091414%22%20target%3D%22_blank%22%3E%40VictoriaIwinski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%20a%20helper%20column.%20Let's%20say%20the%20replies%20are%20in%20B2%3AZ500.%3C%2FP%3E%0A%3CP%3EIn%20an%20empty%20column%2C%20for%20example%20column%20AB%2C%20enter%20the%20following%20formula%20in%20row%202%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(B2%3AZ2%2C%22Yes%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20row%20500%20(the%20last%20row%20with%20data).%3C%2FP%3E%0A%3CP%3EThe%20number%20of%20rows%20with%20at%20least%20one%20%22Yes%22%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(AB2%3AAB500%2C%22%26gt%3B0%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2503128%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20how%20many%20rows%20have%20a%20specific%20text%20choice%2C%20but%20counting%20it%20only%20once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2503128%22%20slang%3D%22en-US%22%3EBasically%20applying%20same%20principle%20as%20previous%20answer%2C%20you%20can%20also%20put%20the%20helper%20column%20formula%20as%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(COUNTIF(B2%3AZ2%2C%22Yes%22)%26gt%3B0%2C1%2C0)%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20drag%20it%20down.%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20SUM%20of%20this%20helper%20column%20is%20the%20desired%20result%2C%20i.e.%20rows%20with%20at%20least%20one%20%22Yes%22.%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello Community. 

 

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

 

VictoriaIwinski_0-1625004221269.png

 

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

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

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

@VictoriaIwinski alternatively a single equation could be:

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