PC Review


Reply
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

 
Reply With Quote
 
 
 
 
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
>
>.
>

 
Reply With Quote
 
 
 
 
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
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct instead of SumifS in VBA (application.sumproduct) Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM
sumproduct? sumif(sumproduct)? =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM
SUMPRODUCT - NO SUMPRODUCT! =?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 08:56 AM.