Sumif() and Sumifs() Issue/Question

[Consagrado]

Hi All,

This is my first post and I have a question regarding the technical feasibility of this request:

Can I use the Sumif() or Sumifs() formula to add things together by not the whole cell but just the last two pieces of text in the field? Example:

QuestionFormn.xlsx

I need to do it in one formula and cant add any other columns. is this possible? The real example I am using is several hundred lines long, so just selecting and adding is not an option.

Id appreciate the help

[Tony Valko]

One way...

=SUM(SUMIFS(C3:C11,B3:B11,{"*10","*12"}))

[ChemistB]

Another way (Array formula: must use CNTRL SHFT ENTER to enter. If done properly, you'll see {} around formula)

=SUM(IF(RIGHT(B3:B11,2)="10",C3:C11)+IF(RIGHT(B3:B11,2)="12",C3:C11))
Although I prefer Tony's way.

[Colin Legg]

In this case you can use SUMIF rather than SUMIFS.

=SUM(SUMIF(B3:B11,{"*10","*12"},C3:C11))

[Consagrado]

Thanks Tony and everyone else that replied! It worked like a charm!

I do have a question involving the logic behind the formula. The array potion {"*XXX","*XYX"} is doing what exactly? Is it looking for any cell within the array that contains either XXX or XYX, or is it looking only at the last characters?

[Colin Legg]

The * is a wildcard which represents one or more characters. So "*10" will check for anything which ends with "10". That could be "a10" or "hello10" or "10". It's important to note that it will only work when the cells contain strings (text), so it wouldn't count a cell which contains the number type 10010, but it would work if the cell contains the string type "10010".

You might want to have a read through a blog post I did a while back which explains pretty much everything you'll ever want to know about SUMIF:
http://colinlegg.wordpress.com/2012/...umif-formulas/

[Tony Valko]

Thanks Tony and everyone else that replied! It worked like a charm!

You're welcome. Thanks for the feedback!