SUMIF exluding text

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?

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)

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.

"Hanzo" wrote:

>
> 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)
>
>
> --
> Hanzo
> ------------------------------------------------------------------------
> Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
> View this thread: http://www.excelforum.com/showthread...hreadid=496536
>
>

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!

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)
"Hanzo" wrote:

>
> 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!
>
>
> --
> Hanzo
> ------------------------------------------------------------------------
> Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
> View this thread: http://www.excelforum.com/showthread...hreadid=496536
>
>

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!

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,

"Father" wrote:

> 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?

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!

Looks like you need some wildcards in there:
=SUMIF($B$2:$B$13,"<>*m*",$A$2:$A$13)

"Father" wrote:

> 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)
> "Hanzo" wrote:
>
> >
> > 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!
> >
> >
> > --
> > Hanzo
> > ------------------------------------------------------------------------
> > Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
> > View this thread: http://www.excelforum.com/showthread...hreadid=496536
> >
> >

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

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.

"Ashish Mathur" wrote:

> 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,
>
> "Father" wrote:
>
> > 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?

Is is possible to combine the two formulas together?
=SUMIF(K2:K5297, "January",J2:J5297)
=SUMPRODUCT(J2:J5297,--(A2:A5297<>"Automotive Services"),--(A2:A5297<>"EBusiness"),--(A2:A5297<>"HR Technology"),--(A2:A5297<>"Membership Tech team"),--(A2:A5297<>"PMO"),--(L2:L5297<>"2015"),--(L2:L5297<>"2017"))