Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 26

Thread: Is this a job for SUMPRODUCT? Or is it easier?

  1. #1

    Is this a job for SUMPRODUCT? Or is it easier?

    I have done a lot of reading on the SUMPRODUCT as a Countif formula.

    However, my ranges are not both columns; so I do not know if they will be multiplicatively conformable.

    Here is the task:

    I have the following:

    WorkSheet named "X"
    WorkSheet named "Y"
    WorkSheet named "Z"

    If the i-th Row in Column B in WorkSheet "X" is a "T" then I would like to Count all of the cells in the i-th row of "Y" that are <= some cell in "Z". Let's call it 'Z'!A1 for now.


    After typing this out, I get the feeling that it might be easier than I am making it.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Try

    =IF(X!Bi="T",COUNTIF(Y!i:i,"<="&Z!A1),"")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    I have not tried that yet xld. But, I think I left out something important that I am not sure how to convey.

    I want to apply this to a fixed RANGE. My goal is to start categorizing the data.

    I have attached a sample that uses 2 countifs as a way to convey the desired results.

    Check out Sheet "Z" first.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Wow. Just Wow...

    xld, can you explain to me some of the details of this? I understand the whole true=1 and false=0. I am fine with that.

    But, what are these cell references ? I have seen $B$1 before (absolute ref).

    But what is this: Y!$1:$3 ? ---->sheet "Y"--->?--->?

    EDIT: Got it I think. Rows 1 - 3.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Y!$1:$3 is just rows 1 through 3 on sheet Y, I assume your real sheet will have more rows.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Yes it does. Hmm. I amstill trying to understand this, so that I can change it as needed.

    So, in this case, the function SUMPRODUCT only has ONE argument right?

    And that argument is just whatever the result of

    (ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T")
    is,correct?

    If so, I have 2 more quick questions. But I will wait first.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    I am not sure what you mean by it just has one argument.

    It has 3 conditions

    (ISNUMBER(Y!$1:$3)) - only include numeric values in rows 1:3, i.e. not blanks

    (Y!$1:$3<=Z!A1) - only include values in rows 1:3 <= A1 on Z

    (X!$B$1:$B$3="T") - only include rows where the corresponding value in column B of X is a T

    The three conditions are ANDed (*), to give an array of 1s and 0s which are summed.
    Last edited by Bob Phillips; 09-23-2009 at 11:22 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Right. So you are basically multiplying 3 arrays, each of which resulted from each of the 3 conditions.

    I guess I am just wondering why you could not just replace SUMPRODUCT with SUM, since you are really only 'feeding' it 1 array?

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    You aren't really feeding it one array, you are ANDing 3 arrays. This

    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1))
    is the same as this

    =SUMPRODUCT(--(ISNUMBER(Y!$1:$3)),--(Y!$1:$3<=Z!A1))
    but you cannot use the latter form when you introduce the 3rd condition, as it is a different shape than the other two.

    And yes, you could use SUM but you would have to array enter it. But that is true of most if not all SUMPRODUCT solutions.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Ok. I see. I will need to find some documentation on what exactly 'ANDing' means.

    So, out of curiousity: Do you know how it does handle the 3rd array? Does 'ANDing' mean that Excel somehow detects the 3rd Array's shape and reshapes it to be compatible?

    EDIT: I kind of see what is happening now.

    The size of the Array: (ISNUMBER(Y!$1:$3)

    is equal to the size of

    (Y!$1:$3<=Z!A1)

    So it ANDs them. Then the 3rd is an absolute array reference that results in either True=1 or False=0.

    Then I get lost.....but it is becoming clearer.
    Last edited by Saladsamurai; 09-23-2009 at 11:47 AM.

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by Saladsamurai
    Ok. I see. I will need to find some documentation on what exactly 'ANDing' means.
    See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

    Quote Originally Posted by Saladsamurai
    So, out of curiousity: Do you know how it does handle the 3rd array? Does 'ANDing' mean that Excel somehow detects the 3rd Array's shape and reshapes it to be compatible?
    Excel doesn't detect the shape, you told it when you stated the ranges to test. Because it is multiplying the arrays, when it sees a horizontal range/array and a vertical range/array, it creates a matrix array result, which gets summed as per normal. That is why you cannot use the double unary form, because that is using SUMPRODUCT in its standard form, which will not multiply the different shaped arrays.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Ok thanks! Also, I just wanted to check. When I use this
    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))
    and I drag the fill handle downwards. Each count also includes those from the count preceding it right?

    That is:

    In order to find the count in a particular class, I would have to subtract the count from the one before it.

    I think I have answered by own question.

    So, I presume that if I want the total of ALL cells in "Y" who have a corresponding "T" in "X" in their respective row designations I would leave off the middle condition.

    So
    Total =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(X!$B$1:$B$3="T"))
    Last edited by Saladsamurai; 09-24-2009 at 05:51 AM.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by Saladsamurai
    Ok thanks! Also, I just wanted to check. When I use this
    [vba]=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))[/vba]

    and I drag the fill handle downwards. Each count also includes those from the count preceding it right?

    That is:

    In order to find the count in a particular class, I would have to subtract the count from the one before it.

    I think I have answered by own question.
    I cannot see wht you would copy down, you only need a single count.

    Quote Originally Posted by Saladsamurai
    So, I presume that if I want the total of ALL cells in "Y" who have a corresponding "T" in "X" in their respective row designations I would leave off the middle condition.

    So [vba]Total =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(X!$B$1:$B$3="T"))[/vba]
    I would use SUMIF here.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Quote Originally Posted by xld
    I cannot see wht you would copy down, you only need a single count.
    No. That Counts all those with values <=-100%

    I also need those
    <=-90%
    <=-80%
    <=-70%

    All the way through +100 %

    So inorder to get those between -100% and -90% I would need to calculate

    (Those <=-90%) - (Those <=-90%)

    You dig?

    I would use SUMIF here.
    Ok. I had already used SUMPRODUCT before you posted this and it works. Is there a performance penalty?

    Actually, I don't see how to use SUMIF here. The range sizes are not equal.

    How do I tell it I want the total of all cells in 'Y' whose row number = row number in 'X' that contains "T" ?

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    You are right, you cannot use SUMIF here, but where possible SUIF should be used, there is a large performance hit using SUMPRODUCT, or any array formula.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  17. #17
    Ok great. Thank you so much for all of your help xld! I have another, what I hope is a 'quick' question.

    I would like to modify the condition:

    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))
    such that instead of = "T"

    it could be

    ="C" OR ="K"

    with the 1st two conditions still the same.

    The 1st two conditions are still the same though.



    And while I am here, I would need another one such that instead of

    = "T"

    it must be

    ="C" AND ="K"

  18. #18
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by Saladsamurai
    [vba]=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))[/vba]

    such that instead of = "T"

    it could be

    ="C" OR ="K"

    with the 1st two conditions still the same.
    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(ISNUMBER(MATCH(X!$B$1:$B$3 ,{"C","K"},0))))

    Quote Originally Posted by Saladsamurai
    And while I am here, I would need another one such that instead of

    = "T"

    it must be

    ="C" AND ="K"
    it can't be C and K, if it is C it is NOT K and vice versa.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  19. #19
    Quote Originally Posted by xld
    =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(ISNUMBER(MATCH(X!$B$1:$B$3 ,{"C","K"},0))))
    OK. I used (Sheet Names Changed, Sorry)

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")+('Cooling Type'!$A$1:$A$4="K")))
    and it seems to work. Sorry..."C" or "K" would be found in column "A" not "B"

    it can't be C and K, if it is C it is NOT K and vice versa
    Oops. I meant (Column A is a "C" or "K") AND (Column B is a "T")

    for which I used

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T")))
    which also works fine.

    Not pretty, but they work.

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by Saladsamurai
    OK. I used (Sheet Names Changed, Sorry)

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")+('Cooling Type'!$A$1:$A$4="K")))
    and it seems to work. Sorry..."C" or "K" would be found in column "A" not "B"
    Mine is neater! But that is sophisticated SP for someone who supposedly doesn't understand it.

    Quote Originally Posted by Saladsamurai
    Oops. I meant (Column A is a "C" or "K") AND (Column B is a "T")

    for which I used

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T")))
    which also works fine.
    Dont' you mean

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$IV$4))*('Cold Difference'!$A$1:$IV$4<=Charts!A1)
    *(ISNUMBER(MATCH('Cooling Type'!$A$1:$A$4,{"C","K"},0)))*('Cooling Type'!$B$1:$B$4="T"))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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