Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |