Sumifs - Help Please

[PhilWong]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Sales
[/TD]
[TD]Sales Code
[/TD]
[/TR]
[TR]
[TD]Phil 1
[/TD]
[TD]£123
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Phil 2
[/TD]
[TD]£123
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Phil 3
[/TD]
[TD]£123
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]Phil 4
[/TD]
[TD]£123
[/TD]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in Advance!

As part of larger sumifs formula I want excel to do the following.

In column A is a list of names, in column B is their sales for the month figure and in column C is list of Sales codes.

I want to be able to sumifs in the Sales code does not end with a number 2.

So in the above example I want the sales for Phil 1 & Phil 4

Any help would be greatly appreciated

Phil

 

[steve the fish]

See if this does it:

=SUMPRODUCT(B2:B5,--(RIGHT(C2:C5)<>"2"))

 

[PhilWong]

Thank you for such a quick response

As this forms part of a number of Sumifs in a much larger formula which part of the formula would I include? Is it just the part in brackets?

 

[Fluff]

To the best of my knowledge, you cannot use RIGHT inside a SUMIFS.

 

[Ramballah]

To the best of my knowledge, you cannot use RIGHT inside a SUMIFS.

thats what i thought too, when i copied this table to my own excel and tried that solution with the right inside the sumproduct it worked. Maybe sumproduct is not considered as a sumif?

 

[Fluff]

Sumproduct & Sumif(s) are completely different functions.