Sumproduct Formula - How Do I Exclude Values

[winnie_shrub]

I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.

=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$B$119:$B$417))

Can anyone advise how I do this please?

[DonkeyOte]

Simply add another condition:

=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$U$119:$U$417<>"Cancelled"),Register!$B$119:$B$417)

Also if the last range are numeric values you don't need to coerce with double unary.

[dominicb]

Good morning winnie_shrub

Just insert it as an extra condition into your formula - untested, but something like this should work fine :

=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$U$119:$U$417<>"Cancelled"),--(Register!$B$119:$B$417))

HTH

DominicB

[winnie_shrub]

Thanks guys.

I didn't realise <> indicates does not equal. Formula amended and working as expected.

Will remember that in future