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?

SUMIF exluding text
 

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

SUMIF exluding text
 

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

SUMIF exluding text
 

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

SUMIF exluding text
 

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

SUMIF exluding text
 

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?

SUMIF exluding text
 

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! :)


--
Hanzo
------------------------------------------------------------------------
Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
View this thread: http://www.excelforum.com/showthread...hreadid=496536

SUMIF exluding text
 

Looks like you need some wildcards in the
=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

SUMIF exluding text
 

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! :)


--
Hanzo
------------------------------------------------------------------------
Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
View this thread: http://www.excelforum.com/showthread...hreadid=496536

SUMIF exluding text
 

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


--
Hanzo
------------------------------------------------------------------------
Hanzo's Profile: http://www.excelforum.com/member.php...o&userid=29955
View this thread: http://www.excelforum.com/showthread...hreadid=496536

SUMIF exluding text
 

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?