Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SUMPRODUCT() question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A while back, on this board Aladin kindly demonstrated for me how to use sumproduct as a conditional count better and more powerful than countif(). I think I am getting the hang of how it works, but need some further help. I do the following to count where two conditions in two seperate columns are true:

    =SUMPRODUCT((A1:A50>5)*(B1:B50>10))

    I think what happens is that each array is evaluated, then the two are multiplied. So 1*1 (for true values) would = 1. And 0*0 or 0*1 would = 0. Then they are all added together, to produce a count where ">5" AND ">10" are true. Am I right so far on the mechanisms of how this works?

    I am also curious on how I could perform an "or" count in the same fashion. I thought about using:

    =SUMPRODUCT((A1:A50>5)+(B1:B50>10))

    But the problem is that the 1+1's = 2. Thus giving an incorrect final count.

    I tried using countif() and OR(), but I couldnt get it to work. So I am looking to sumproduct() for a solution. Am I looking in the right place?

    Thanks for any help!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Are you sure that your SumProduct formula did not work?

    A very small example and your exact formula would be useful.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The top SUMPRODUCT() did exactly what I wanted. So, yes it works. But I'm trying to do the same thing, except with "OR" logic, instead of "AND". The multiplication of the 2 arrays produces a 1 when both values are true and a 0 otherwise. (So each row is counted when column A >5, _AND_ column B >10)

    What I want, is the two arrays to be evaluated, but to produce a 1 when EITHER
    condition is true, not just both.

    So column A has 50 rows, column B has 50 rows. Each is full of numbers. I want to count each row that contains a value >5 in column A, _OR_ >10 in column B.

    Like I said, I am looking to sumproduct, because this seems impossible with countif().
    Maybe its impossible with sumproduct as well.
    Thats my question.

    I hope I have been clear. Sorry if I havent.

    Also, I dont have a real idea for a formula on how to do this... The formula in my above post (the sumproduct with +), is the only thing I could think of trying. It obviously doesnt do the job.



    [ This Message was edited by: John McGraw on 2002-03-18 23:32 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-18 23:23, John McGraw wrote:
    The top SUMPRODUCT() did exactly what I wanted. So, yes it works. But I'm trying to do the same thing, except with "OR" logic, instead of "AND". The multiplication of the 2 arrays produces a 1 when both values are true and a 0 otherwise. (So each row is counted when column A >5, _AND_ column B >10)

    What I want, is the two arrays to be evaluated, but to produce a 1 when EITHER
    condition is true, not just both.

    So column A has 50 rows, column B has 50 rows. Each is full of numbers. I want to count each row that contains a value >5 in column A, _OR_ >10 in column B.

    Like I said, I am looking to sumproduct, because this seems impossible with countif().
    Maybe its impossible with sumproduct as well.
    Thats my question.

    I hope I have been clear. Sorry if I havent.

    Also, I dont have a real idea for a formula on how to do this... The formula in my above post (the sumproduct with +), is the only thing I could think of trying. It obviously doesnt do the job.

    [ This Message was edited by: John McGraw on 2002-03-18 23:32 ]
    Hi John,

    The SUMPRODUCT version would be:

    [1]

    =SUMPRODUCT(1*((A1:A50>5)+(B1:B50>10)))

    However,

    [2]

    =COUNTIF(A1:A50,">5")+COUNTIF(B1:B50,">10")

    will compute the same result.

    Aladin

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 23:45, Aladin Akyurek wrote:
    Hi John,

    The SUMPRODUCT version would be:

    [1]

    =SUMPRODUCT(1*((A1:A50>5)+(B1:B50>10)))

    However,

    [2]

    =COUNTIF(A1:A50,">5")+COUNTIF(B1:B50,">10")

    will compute the same result.

    Aladin
    Thanks Aladin. But I dont think it works. I'm afraid that maybe I wasnt clear enough. I'm sorry for that.

    COL A = {01;06;07;01;08;03;05}
    COL B = {01;11;12;01;01;01;03};

    I only put 0's before single digits so the values would line up and be easier to compare.

    Now, if I count A>5 OR B>10; The answer would be 3. But:

    =SUMPRODUCT(1*((A1:A7>5)+(B1:B7>10)))

    would produce the answer of 5. It is counting rows with two values true twice, thats not OR logic. The countif() solution does the same thing.

    I hope I make sense. If a1>5, and b1>10, I only want to count that row once, not twice. Traditional OR logic.

    Thanks much for the help, it is appreciated greatly!


  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-19 00:18, John McGraw wrote:
    On 2002-03-18 23:45, Aladin Akyurek wrote:
    Hi John,

    The SUMPRODUCT version would be:

    [1]

    =SUMPRODUCT(1*((A1:A50>5)+(B1:B50>10)))

    However,

    [2]

    =COUNTIF(A1:A50,">5")+COUNTIF(B1:B50,">10")

    will compute the same result.

    Aladin
    Thanks Aladin. But I dont think it works. I'm afraid that maybe I wasnt clear enough. I'm sorry for that.

    COL A = {01;06;07;01;08;03;05}
    COL B = {01;11;12;01;01;01;03};

    I only put 0's before single digits so the values would line up and be easier to compare.

    Now, if I count A>5 OR B>10; The answer would be 3. But:

    =SUMPRODUCT(1*((A1:A7>5)+(B1:B7>10)))

    would produce the answer of 5. It is counting rows with two values true twice, thats not OR logic. The countif() solution does the same thing.

    I hope I make sense. If a1>5, and b1>10, I only want to count that row once, not twice. Traditional OR logic.

    Thanks much for the help, it is appreciated greatly!
    Yep, you got me there.

    Try:

    =SUMPRODUCT((1*((A1:A7>5)+(B1:B7>10))))-SUMPRODUCT((A1:A7>5)*(B1:B7>10))

    Aladin

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi John

    I haven't followed this thread but try this:

    Let's say Column A has the Heading "Head1" and B the heading "Head2"

    Copy these headings to cells to D1:E1
    then in cell D2 put: >5 and cell E2 put: <10

    Again copyn the 2 headings, this time to cells F1:G1
    then in cell F2 put: >5 and cell G2 put: >10


    Now in any cell put:
    =SUM(DCOUNT(A1:B8,"Head1",D1:E2),DCOUNT(A1:B8,"Head1",F1:G2))

    The database Functions offer a lot of flexibility and can be seen here:
    http://www.ozgrid.com/download/default.htm
    Download: DFunctionsWithValidation.zip

    With a bit of imagination you can make are very user friendly spreadheet that will extract your data from a data table that needs to meet up to 256 Criteria.



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-19 02:48 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    is that any 256 criteria or 256 rows of criteria in the criteria range ?

    I assume that if we had, say, 400, we could just split it into 2 seperate D functions ?

    *please* say yes !

    Chris


    edit.... oh, 256 criteria columns in the criteria range, you mean ?

    [ This Message was edited by: Chris Davison on 2002-03-19 05:35 ]

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris

    Yes I mean 256 columns of table headings and 256 criteria under each. But as you say you could use 2 database functions.



  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    400... man, those are a lot of criteria !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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