# complex sumif formula needed

#### ShanaVT

##### Board Regular
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 Watts, Alan Wrong Watts, Alan OK Watts, Alan OK Williams, Kelvin OK Williams, Kelvin Wrong Williams, Kelvin Wrong Williams, Kelvin OK Williams, Kelvin OK Williams, Kelvin Wrong Young, Alvin OK Young, Alvin OK Young, Alvin Wrong Young, Alvin Wrong Young, Alvin Wrong Young, Alvin OK Young, Alvin

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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

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

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

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

Replies
7
Views
443
Replies
5
Views
969
Replies
9
Views
770
Replies
4
Views
344
Replies
7
Views
432

1,196,369
Messages
6,014,885
Members
441,854
Latest member
Amstaff

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back