sumif question....

VTTW · May 17th, 2002, 05:26 AM

I7:I200 contain a number ranging from 1-10. In K7:K200 I have either L, H or is left blank. Y7:Y200 contain another set of numbers.

Is it possible for sumif to handle two criteria. For instance, sumif totals only the numbers in Y7:Y200 that have a "5" in col I & an H in col K??

Thanks

WillR · May 17th, 2002, 05:34 AM

Use sumproduct function.

eg

=SUMPRODUCT((Range = value)*(2nd range = 2nd value),Range to SUM)

Hope this makes sense.

Will

Mark W. · May 17th, 2002, 08:56 AM

{=SUM((I7:I200=5)*(K7:K200="H")*Y7:Y200)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Yogi Anand · May 17th, 2002, 09:35 AM

or the following array formula:

=SUM(IF(I7:I200=5,IF(K7:K200="H",Y7:Y200,0)))

Regards!

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

May 17th, 2002, 05:26 AM	#1
VTTW New Member Join Date: Mar 2002 Posts: 26	I7:I200 contain a number ranging from 1-10. In K7:K200 I have either L, H or is left blank. Y7:Y200 contain another set of numbers. Is it possible for sumif to handle two criteria. For instance, sumif totals only the numbers in Y7:Y200 that have a "5" in col I & an H in col K?? Thanks

May 17th, 2002, 05:34 AM	#2
WillR Board Regular Join Date: Feb 2002 Location: Sutton Coldfield Posts: 1,143	Use sumproduct function. eg =SUMPRODUCT((Range = value)*(2nd range = 2nd value),Range to SUM) Hope this makes sense. Will

May 17th, 2002, 08:56 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	{=SUM((I7:I200=5)(K7:K200="H")Y7:Y200)} Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

May 17th, 2002, 09:35 AM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	or the following array formula: =SUM(IF(I7:I200=5,IF(K7:K200="H",Y7:Y200,0))) Regards!