Sumifs - Help Please

[PhilWong]

Name	Sales	Sales Code
Phil 1	£123	10
Phil 2	£123	12
Phil 3	£123	22
Phil 4	£123	20

<tbody>
</tbody>

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.