SUMPRODUCT and using OR(....)

Discussion in 'Microsoft Excel Worksheet Functions' started by Marc, Sep 25, 2003.

  1. Marc

    Marc Guest

    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
     
    Marc, Sep 25, 2003
    #1
    1. Advertisements

  2. Marc

    mikelee Guest

    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
    >
    >.
    >
     
    mikelee, Sep 25, 2003
    #2
    1. Advertisements

  3. 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" <> wrote in message
    news:088e01c3839f$ccca6200$...
    > 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, Sep 25, 2003
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Using IF and SUMPRODUCT??

    Guest, Feb 4, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    140
    Guest
    Feb 5, 2005
  2. Guest

    Repost of Using IF and SUMPRODUCT

    Guest, Feb 7, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    141
    Guest
    Feb 10, 2005
  3. Guest

    sumproduct? sumif(sumproduct)?

    Guest, Jul 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    638
    Guest
    Jul 13, 2007
  4. Ted M H

    Conditional SUMPRODUCT or SUMPRODUCT with Filters

    Ted M H, Aug 14, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    1,838
    Ted M H
    Aug 14, 2008
  5. KCR

    Using same fomula to count AND and OR options / sumproduct

    KCR, Dec 12, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    141
    Bernard Liengme
    Dec 12, 2008
Loading...

Share This Page