Thread Tools Rate Thread

# SUMPRODUCT and using OR(....)

Marc
Guest
Posts: n/a

 25th Sep 2003
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

mikelee
Guest
Posts: n/a

 25th Sep 2003
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
>
>.
>

Aladin Akyurek
Guest
Posts: n/a

 25th Sep 2003
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
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM

Features

Advertising

Newsgroups

All times are GMT +1. The time now is 05:57 AM.