COUNTIF or SUM function (Multiple criteria) HELP!!

A

Australia

HELP!
I need an array that will COUNT the number of occurrences of multipl
criteria across two columns.

Example:
So, in the simplified example below, it would only COUNT occurrence
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 o
Amber, but don't know how to incorporate the OR function for the othe
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 FILTERIN
CRITERIA?

_FRUIT_ _COLOUR
Ripe Red
Inedible Red
Ripe Amber
Rotten Amber
Picked Amber
Ready Red
Cooked Green
Cooked Red
Rotten Yello
 
A

Aladin Akyurek

=SUMPRODUCT(--ISNUMBER(MATCH(FruitRange,{"Ripe", "Ready",
"Cooked"},0)),--ISNUMBER(MATCH(ColourRange,{"Amber","Red"},0)))
 
B

Biff

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))*((Color=G1)+(Color=G2)))

Biff
 
A

Australia

THANKS Aladin! Much appreciated! You made it look so easy!! I fee
really silly about spedning all morning on it now!

Thanks again.



Aladin said:
=SUMPRODUCT(--ISNUMBER(MATCH(FruitRange,{"Ripe", "Ready",
"Cooked"},0)),--ISNUMBER(MATCH(ColourRange,{"Amber","Red"},0)))
HELP!
I need an array that will COUNT the number of occurrences o 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' o '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 th 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
Click to expand...
Click to expand...
 
Top