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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest

    Using IF and SUMPRODUCT??

    Guest, Feb 4, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    215
    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:
    260
    Guest
    Feb 10, 2005
  3. rlutes

    SUMPRODUCT using and INDEX function doesn't total

    rlutes, Jun 4, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    346
    Domenic
    Jun 5, 2005
  4. Guest

    Using SumProduct and Count To Calculate % Ranks

    Guest, Apr 28, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    345
    Guest
    Apr 28, 2006
  5. Guest

    sumproduct? sumif(sumproduct)?

    Guest, Jul 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    1,136
    Guest
    Jul 13, 2007
  6. Guest

    Problem with using INDIRECT with SUMPRODUCT and ROW()

    Guest, Nov 1, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    11
    Views:
    285
    RagDyeR
    Nov 3, 2007
  7. Ted M H

    Conditional SUMPRODUCT or SUMPRODUCT with Filters

    Ted M H, Aug 14, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    4,837
    Ted M H
    Aug 14, 2008
  8. KCR

    Using same fomula to count AND and OR options / sumproduct

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