Exclusion in SumIF

Nimrod · May 24th, 2002, 02:05 PM

Help Please

I'm wanting to Sum All the values in ColB excluding numbers in ColB when the corresponding Cell in ColA has the value "xyz" in it ... any ideas ?

Yogi Anand · May 24th, 2002, 02:08 PM

=SUMIF(A:A,"<>xyz",B:B)

Jay Petrulis · May 24th, 2002, 02:10 PM

Try,

=SUMPRODUCT((ISERROR(SEARCH("xyz",A1:A10,1)))*(B1:B10))

Adjust the range to suit.

Bye,
Jay

EDIT: Yogi's solution is nicer. Disregard this one unless "xyz" can be found within other strings.

[ This Message was edited by: Jay Petrulis on 2002-05-24 13:35 ]

Nimrod · May 24th, 2002, 02:45 PM

Thanks alot ... you guys were a great help.

Nimrod · May 24th, 2002, 02:47 PM

Amaziiiiing , I actually have another situation that I need to exclude a subset string so I've ended up needing both solutons... again many thanks

May 24th, 2002, 02:47 PM	#5
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Amaziiiiing , I actually have another situation that I need to exclude a subset string so I've ended up needing both solutons... again many thanks __________________ <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 02:05 PM	#1
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Help Please I'm wanting to Sum All the values in ColB excluding numbers in ColB when the corresponding Cell in ColA has the value "xyz" in it ... any ideas ?

May 24th, 2002, 02:08 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	=SUMIF(A:A,"<>xyz",B:B)

May 24th, 2002, 02:10 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Try, =SUMPRODUCT((ISERROR(SEARCH("xyz",A1:A10,1)))*(B1:B10)) Adjust the range to suit. Bye, Jay EDIT: Yogi's solution is nicer. Disregard this one unless "xyz" can be found within other strings. [ This Message was edited by: Jay Petrulis on 2002-05-24 13:35 ]

May 24th, 2002, 02:45 PM	#4
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Thanks alot ... you guys were a great help.