SUMPRODUCT and using OR(....)

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

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
 
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))))
 
Back
Top