Clever Geek Handbook

How to count the number of "Yes" in column C once only if the id in column B repeats

I need help counting Yes in column C, but only count it once if the id in column B is repeated

Sample output. for the sample below, the score should be 6.

Here is the link to the Dropbox file. Link to Dropbox file

enter image description here

I can do this already through an if and else condition using a formula, but I wish someone could better come up with a single line formula.

Current solution in cell F2

=IF(B2<>B1,IF(C2="Yes","1",""),IF(B2=B1,IF(F1="1","",IF(B2=#REF!,"",""))))

in cell F3 and below -

=IF(B3<>B2,IF(C3="Yes","1",""),IF(B3=B2,IF(F2="1","",IF(B3=B1,"",""))))

Then I just recount everything and get the total.

=countif(F2:F159,"1")
+3


source to share


2 answers


You can take a similar approach to my previous answer in your link, i.e. use cell references in your Dropbox example

=SUM(IF(FREQUENCY(IF(I2:I14="Yes",H2:H14),H2:H14),1))

confirmed CTRL+SHIFT+ENTER

H2: H14 must be numeric data



In general, if Range 4 is numeric, you can use this formula for three conditions

=SUM(IF(FREQUENCY(IF((Range1="x")*(Range2="y")*(Range3="z"),Range4),Range4),1))

conditions can be added or removed as needed

+5


source


Place this in D2 and drag down:



=IF(COUNTIFS(B$2:B2,B2,C$2:C2,"Yes")=1,1,"don't count")
+1


source






More articles:


All Articles