Excel: SUMIF depending on number in field
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 开发者_Go百科| 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 | 2Now 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.*
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".
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
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.
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.
精彩评论