complex sumif formula needed

[ShanaVT]

I have the below list of data and I need to sum everywhere column A lists "Wrong", however, I only want the formula to return 1 "Wrong" per name listed. Any help much appreciated.

OK</SPAN>	Watts, Alan</SPAN>
Wrong</SPAN>	Watts, Alan</SPAN>
OK</SPAN>	Watts, Alan</SPAN>
OK</SPAN>	Williams, Kelvin</SPAN>
OK</SPAN>	Williams, Kelvin</SPAN>
Wrong</SPAN>	Williams, Kelvin</SPAN>
Wrong</SPAN>	Williams, Kelvin</SPAN>
OK</SPAN>	Williams, Kelvin</SPAN>
OK</SPAN>	Williams, Kelvin</SPAN>
Wrong</SPAN>	Young, Alvin</SPAN>
OK</SPAN>	Young, Alvin</SPAN>
OK</SPAN>	Young, Alvin</SPAN>
Wrong</SPAN>	Young, Alvin</SPAN>
Wrong</SPAN>	Young, Alvin</SPAN>
Wrong</SPAN>	Young, Alvin</SPAN>
OK</SPAN>	Young, Alvin</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[Weazel]

something like...

=SUM(IF(FREQUENCY(IF($A$2:$A$17="wrong",MATCH(B2:B17,B2:B17,0)),ROW(A2:A17)-ROW(A2)+1),1)) control shift enter

would return a count of 3

though I'm not sure that's exactly what you're looking for

 

[Aladin Akyurek]

something like...

=SUM(IF(FREQUENCY(IF($A$2:$A$17="wrong",MATCH(B2:B17,B2:B17,0)),ROW(A2:A17)-ROW(A2)+1),1)) control shift enter

would return a count of 3

though I'm not sure that's exactly what you're looking for

Add a term for testing: B2:B17 <> "".

 

[Weazel]

Thanks Aladin,

I wasn't sure if it would be necessary to check B for "" to get the count

=SUM(IF(FREQUENCY(IF($A$2:$A$17="wrong",IF(B2:B17<>"",MATCH(B2:B17,B2:B17,0))),ROW(A2:A17)-ROW(A2)+1),1)) Control Shift Enter

 

[Aladin Akyurek]

Thanks Aladin,

I wasn't sure if it would be necessary to check B for "" to get the count

=SUM(IF(FREQUENCY(IF($A$2:$A$17="wrong",IF(B2:B17<>"",MATCH(B2:B17,B2:B17,0))),ROW(A2:A17)-ROW(A2)+1),1)) Control Shift Enter

Always... We don't want a blank as a type (unless explicitly asked for).