countif using multiple criteria

sasats · May 22nd, 2002, 05:52 PM

I saw this question earlier with a sum problem but I need a solution that will work using the countif function.

I need to "countif" two seperate criteria are met... if rangeA=value1 and rangeB=value2.

Thanks for any help.

Juan Pablo González · May 22nd, 2002, 05:53 PM

This is a common request.

Try something like

=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2))

Where Range1 is something like A1:A10, Criteria1 is something like D1 or "Condition". Same applies to Range2 and Criteria2

Yogi Anand · May 22nd, 2002, 06:09 PM

Juan's non-array formula may be better;

Here is an array formula:

{=SUM(IF(A1:A10="criterion1",IF(B1:B10="criterion2",1,0)))}

Regards!

Edit: I just wanted to add that even though the formula uses the SUM function, the formula is actually counting the number of items that meet both the criteria.

[ This Message was edited by: Yogi Anand on 2002-05-22 18:15 ]

[ This Message was edited by: Yogi Anand on 2002-05-22 20:15 ]

May 22nd, 2002, 05:53 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	This is a common request. Try something like =SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2)) Where Range1 is something like A1:A10, Criteria1 is something like D1 or "Condition". Same applies to Range2 and Criteria2 __________________ Regards, Juan Pablo González http://www.juanpg.com

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

May 22nd, 2002, 05:52 PM	#1
sasats New Member Join Date: May 2002 Location: Scott Taylor Posts: 1	I saw this question earlier with a sum problem but I need a solution that will work using the countif function. I need to "countif" two seperate criteria are met... if rangeA=value1 and rangeB=value2. Thanks for any help.

May 22nd, 2002, 06:09 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Juan's non-array formula may be better; Here is an array formula: {=SUM(IF(A1:A10="criterion1",IF(B1:B10="criterion2",1,0)))} Regards! Edit: I just wanted to add that even though the formula uses the SUM function, the formula is actually counting the number of items that meet both the criteria. [ This Message was edited by: Yogi Anand on 2002-05-22 18:15 ] [ This Message was edited by: Yogi Anand on 2002-05-22 20:15 ]