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

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>

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>