|
COUNTIF or SUM function (Multiple criteria) HELP!!
HELP! I need an array that will COUNT the number of occurrences of multiple criteria across two columns. Example: So, in the simplified example below, it would only COUNT occurrences that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked']. So far, I've only been able to come up counting it of it's Red or Amber, but don't know how to incorporate the OR function for the other criteria set. The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0)) * HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING CRITERIA?? _FRUIT_ _COLOUR_ Ripe Red Inedible Red Ripe Amber Rotten Amber Picked Amber Ready Red Cooked Green Cooked Red Rotten Yellow -- AustraliaPosted via http://www.officehelp.in - <a href="http://www.officehelp.in">Microsoft Office Software</a> |
=SUMPRODUCT(--ISNUMBER(MATCH(FruitRange,{"Ripe", "Ready",
"Cooked"},0)),--ISNUMBER(MATCH(ColourRange,{"Amber","Red"},0))) Australia wrote: HELP! I need an array that will COUNT the number of occurrences of multiple criteria across two columns. Example: So, in the simplified example below, it would only COUNT occurrences that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked']. So far, I've only been able to come up counting it of it's Red or Amber, but don't know how to incorporate the OR function for the other criteria set. The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0)) * HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING CRITERIA?? _FRUIT_ _COLOUR_ Ripe Red Inedible Red Ripe Amber Rotten Amber Picked Amber Ready Red Cooked Green Cooked Red Rotten Yellow |
Hi!
Try this: =SUMPRODUCT(((G10:G215="ripe")+(G10:G215="ready")+ (G10:G215="cooked"))*((H10:H215="red")+(H10:H215=" amber"))) If you used cells to hold the criteria and also used named ranges you could shorten that to: =SUMPRODUCT(((Fruit=F1)+(Fruit=F2)+(Fruit=F3))*((C olor=G1)+(Color=G2))) Biff "Australia" wrote in message ... HELP! I need an array that will COUNT the number of occurrences of multiple criteria across two columns. Example: So, in the simplified example below, it would only COUNT occurrences that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked']. So far, I've only been able to come up counting it of it's Red or Amber, but don't know how to incorporate the OR function for the other criteria set. The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0)) * HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING CRITERIA?? _FRUIT_ _COLOUR_ Ripe Red Inedible Red Ripe Amber Rotten Amber Picked Amber Ready Red Cooked Green Cooked Red Rotten Yellow -- AustraliaPosted via http://www.officehelp.in - <a href="http://www.officehelp.in">Microsoft Office Software</a> |
THANKS Aladin! Much appreciated! You made it look so easy!! I feel really silly about spedning all morning on it now! Thanks again. Aladin Akyurek Wrote: =SUMPRODUCT(--ISNUMBER(MATCH(FruitRange,{"Ripe", "Ready", "Cooked"},0)),--ISNUMBER(MATCH(ColourRange,{"Amber","Red"},0))) Australia wrote: HELP! I need an array that will COUNT the number of occurrences of multiple criteria across two columns. Example: So, in the simplified example below, it would only COUNT occurrences that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked']. So far, I've only been able to come up counting it of it's Red or Amber, but don't know how to incorporate the OR function for the other criteria set. The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0)) * HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING CRITERIA?? _FRUIT_ _COLOUR_ Ripe Red Inedible Red Ripe Amber Rotten Amber Picked Amber Ready Red Cooked Green Cooked Red Rotten Yellow -- AustraliaPosted via http://www.officehelp.in - <a href="http://www.officehelp.in">Microsoft Office Software</a> |
| All times are GMT +1. The time now is 10:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com