I am looking for a formula to count several different
values in "Range A", if the corresponding cell in "Range
B" is not empty. However, from the cells in "Range B", I
want to exclude two particular values.

For example:

Model Colour

Golf white
Jetta black
Miata green
Golf blue
Passat
Jetta green

I would like to count Golf, Jetta, Passat, if the colour
is not white or black. The formula should return 2 as the
count.

Thanks

you can do this with an array formula. in the following
example, "model" is a name for the range with the models
in it and "color" is a name for the range with the colors
in it.

=SUM((IF(model="Golf",1,0)+IF(model="Jetta",1,0)+IF
(model="Passat",1,0))*IF(color<>"white",1,0)*IF
(color<>"black",1,0)*IF(color<>"",1,0))

if you enter that into the cell and hit ctrl + shift +
enter instead of enter (to enter it as an array formula)
that will give you what you're looking for.

hope this helps.

mike
>-----Original Message-----
>I am looking for a formula to count several different
>values in "Range A", if the corresponding cell in "Range
>B" is not empty. However, from the cells in "Range B", I
>want to exclude two particular values.
>
>For example:
>
>Model Colour
>
>Golf white
>Jetta black
>Miata green
>Golf blue
>Passat
>Jetta green
>
>I would like to count Golf, Jetta, Passat, if the colour
>is not white or black. The formula should return 2 as
the
>count.
>
>Thanks
>
>.
>

Let A1:B7 house the sample data including the labels.

Try:

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7,{"Golf","Jetta","Passat"},0)))*(B2:B7<>"")
*(ISNA(MATCH(B2:B7,{"White","Black"},0))))

"Marc" <(E-Mail Removed)> wrote in message
news:088e01c3839f$ccca6200$(E-Mail Removed)...
> I am looking for a formula to count several different
> values in "Range A", if the corresponding cell in "Range
> B" is not empty. However, from the cells in "Range B", I
> want to exclude two particular values.
>
> For example:
>
> Model Colour
>
> Golf white
> Jetta black
> Miata green
> Golf blue
> Passat
> Jetta green
>
> I would like to count Golf, Jetta, Passat, if the colour
> is not white or black. The formula should return 2 as the
> count.
>
> Thanks
>