+ Reply to Thread
Results 1 to 2 of 2

Sumproduct

  1. #1
    Registered User
    Join Date
    08-10-2007
    Posts
    12

    Sumproduct

    Hey guys, I have columns. A, B If I'm using sumproduct as a counting function, I need to set up at least two criterias, right? But, if I want to count all the values in column A where column b has a certain value (I don't care about the value in Column A), how can I do it? In other words, I want to count all values in column A (They can be different) as long as there is a certain value in the row for column B.


    Also, How do I modify it to only count DIFFERENT values in column A?

    If you still don't understand, here's an example:

    [CODE]
    A- COlumn 1
    A1- Dog
    A2- Cat
    A3- Horse
    A4- Dog

    B- column 2
    B1- One
    B2- One
    B3- Two
    B4- One

    So, I'm looking for 2 versions of sumproduct.

    1. Count all values in column A only if column B = One
    This would give a result of 3.
    2. Count all DIFFERENT values in column A only if column B=One
    This would give a result of 2

    thx in advance

    mgkmn

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by mgkmn
    Hey guys, I have columns. A, B If I'm using sumproduct as a counting function, I need to set up at least two criterias, right? But, if I want to count all the values in column A where column b has a certain value (I don't care about the value in Column A), how can I do it? In other words, I want to count all values in column A (They can be different) as long as there is a certain value in the row for column B.


    Also, How do I modify it to only count DIFFERENT values in column A?

    If you still don't understand, here's an example:

    [CODE]
    A- COlumn 1
    A1- Dog
    A2- Cat
    A3- Horse
    A4- Dog

    B- column 2
    B1- One
    B2- One
    B3- Two
    B4- One

    So, I'm looking for 2 versions of sumproduct.

    1. Count all values in column A only if column B = One
    This would give a result of 3.
    2. Count all DIFFERENT values in column A only if column B=One
    This would give a result of 2

    thx in advance

    mgkmn
    Hi,

    try somethiong like, in C1 put

    =IF(SUMPRODUCT(--(A$1:A1=A1)*(--(B$1:B1=B1)))>1,"",SUMPRODUCT(--(A$1:A$100=A1)*(--(B$1:B$100=B1))))

    adjust the range 1:100 to suit your data, and formula fill that down the column far enough to cover all unique pairs (or to the end of your data).

    hth
    ---
    Si fractum non sit, noli id reficere.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1