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.

[TABLE="width: 172"]
<TBODY>[TR]
[TD]OK</SPAN>[/TD]
[TD]Watts, Alan</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Watts, Alan</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Watts, Alan</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Williams, Kelvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]Wrong</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
[TR]
[TD]OK</SPAN>[/TD]
[TD]Young, Alvin</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[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).