SUMIF exluding text

F

Father

Hello I am trying to sum data in a column based on the absence of a character
in a related column.
That is to say I need to be able to exclude rows that contain an "m" (there
may be multiple characters in the criteria cell)
Anyone have any ideas?
 
H

Hanzo

I just Had that issue solved!
You can use something like this, just adjust your ranges!

=SUMIF($B$2:$B$12,"",$A$2:$A$12)

In this case, everything in the range B2:B12 that has an empty space
will add the values in their respective A column.
Just beware that instead of NOTHING you don't have a BLANK SPACE, or it
will fail.
If you want to check for BLANK SPACES just change it to

=SUMIF($B$2:$B$12," ",$A$2:$A$12)
 
F

Father

I think I wasn't clear, if the column is blank I add it, if the column has an
"n" in it I add it, If the column has an "m, n" I don't add it (or a "m", or
"m, n, o" ect.) the column can have anything in it and still be added until
there is an "m" present.
 
H

Hanzo

You are right! Sorry! I misread it...
Well, in that case the code you need is

=SUMIF($B$2:$B$13,"<>m",$A$2:$A$13)

The <> symbol means "DIFFERENT FROM".
What it does is SUM everything that is not m.

I guess that's what you really need?
Sorry for the inconvinience!
 
F

Father

Still no go :)

=SUMIF($B$2:$B$13,"<>m",$A$2:$A$13) will exclude "m" but will include "m, n"
I need it to exclude both (as well as other combinations with 'm' in them)
 
A

Ashish Mathur

Hi,

You may also try the following array formula (Ctrl+Shift+Enter). This is in
range B14:C17

b 1
n 2
m 3
a,m 4

=sum(C14:C17)-SUM(IF(ISNUMBER(FIND(B16,B14:B17)),C14:C17))

Regards,
 
H

Hanzo

LOL!
Ok, here is the real deal!
THere are different ways to do this, but this one is a lot simplier:

=SUMPRODUCT($A$2:$A$13,--($B$2:$B$13<>"m"),--($B$2:$B$13<>"m,n"))

Now, A2:A13 is your range to add. B2:B13 is the range you want to
check.
As you can see, the <> is present again to indicate DIFFERENT.
You can add as many as you need, like

=SUMPRODUCT($A$2:$A$13,--($B$2:$B$13<>"m"),--($B$2:$B$13<>"m,n"),--($B$2:$B$13<>"anything"),--($B$2:$B$13<>"whatever"),--($B$2:$B$13<>"etc"))

Hope this one makes it! :)
 
H

Hanzo

Yep, it's your choice. As I said, there are different ways to do it.
How ever, I found the SUMPRODUCT way easier because it works with
arrays, while SUM needs to be especified by the CTRL+SHIFT+ENTER
combination, and if you are editing your formula regulary and forget
the combination, you'll get an awfull #VALUE error.

Anyway, take what you find more convenient to your personal dilemma! :)
 
H

Hanzo

It works if you want to bypass anything that has an m anywhere.
It is another good option.
However, it depends on your needs.
 
F

Father

This is close to what I'm looking for, however FIND(B16,B14:B17) does not
seem to work, it reurns #value. FIND(B16,B17) does work.
 
Top