Excel: SUMIF depending on number in field

Question

Hi All You Amazing People

Update

You know what, I should let you know that I am actually trying to do this with numbers and not alphabets. For instance, I have a field with value like 225566 and I am trying to pick out fields which have 55 in them. It is only now I realize this might make a huge difference

ColumnA | ColumnB |
225566 | 2
125589 | 3
95543 | 2

(Below is what I had asked first and later realized I wasn't asking the right question.) *Lets say I have a table as

ColumnA | ColumnB |
AABBC | 2
AADDC | 3
ZZBBC | 2

Now how could I get a SUMIF for those rows where Column A has a field with BB in it? Assume that there are hundreds of rows. I realize that I have to borrow something conceptually from the way text to column is done. But I wonder if anyone would know how I could do this. Thanks a lot.*

Answer 1

In Excel 2003 and 2007 (and possibly earlier versions, I cannot test), you can use * as a wildcard character in the match. For example, with your sample data set C1 to

=SUMIF(A1:A3,"*BB*",B1:B3)

and you should see the value 4.

Answer 2

Since you're trying to do this on numbers, you'll need to use an array formula.

If your test values are in A3:A5 and your values to sum are in B3:B5, this will work:

=SUM( IF(ISERROR(FIND("55", TEXT(A3:A5,"#"))), 0, 1) * B3:B5 )

When entering an array formula, use Ctrl-Shift-Enter rather than just hitting Enter.

This sums the product of the sum value and a 0 or 1 from the IF() statement, which tests whether or not each test value, after being converted to text, contains a "55".

Answer 3

I think you will need an matrix/array formular to do this:

{=SUM(IF(ISERROR(FINDEN("55";A2:A4;1));0;1))}

The weird brakets {} indicate it is an matrix formular you get them by pressing SHIFT+CTRL+RETURN instead of Return when editing the formula.

This formula will cycle through the range A2:A4, check if it finds "55" inside and if so add 1 to the sum.

Google array/matrix formulas as they are not self explanatory.

Best

Jan

Answer 4

Create a 3rd column (ColumnC) and put this formula in it:

=Text(A2,0)

Drag that column down to complete your column. This will format the value as text. Next, use SUMIF as DocMax explained, except with different columns:

=SUMIF(C1:C3,"*BB*",B1:B3)

The reason you do this is because you need to be reading a Text value, not a Number value when using the *BB* comparison of SUMIF. Great question.