Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 26 of 26

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

  1. #21
    Quote Originally Posted by xld


    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"))
    I am not sure what you are doing with the 'MATCH' as I have never used it. DO I need it? (presuming I don't care about neatness for now)

    I am not sure what part of my
    =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")))
    in particular you are correcting? Could you maybe boldface it?



    I have the condition:
    (ISNUMBER( 'Cold Difference'!$1:$4))
    ANDed with the condition

    ('Cold Difference'!$1:$4<=Charts!A1)
    ANDed with the resultant of the two conditions

    (('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T"))
    Updated sample file attached. You can see that on the "Charts" sheet, everything works out.

  2. #22
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You said it was C or K, but you are only testing for C, so I changed that.
    ____________________________________________
    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. #23
    Quote Originally Posted by xld
    You said it was C or K, but you are only testing for C, so I changed that.
    Ahh yes.... It should test for

    ((Isnumber)*( <= some number)) AND ((=C or =K) AND ( = T))
    So if I were to use my "super messy version" it would be:

    =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"))* _
    ('Cooling Type'!$B$1:$B$4="T")))


    Without all the whitespace obviously. Just trying to make it legible.

    Neatness aside, that works right?

    P.S. Why did you use the 2nd 'ISNUMBER' function? How does that work? It appears that
    you are testing to see if there is a number, but there is not....there is a "C" or a "K"....

    P.P.S. I presume this was a typo right?

    =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"))

  4. #24
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Saladsamurai
    Ahh yes.... It should test for

    ((Isnumber)*( <= some number)) AND ((=C or =K) AND ( = T))
    So if I were to use my "super messy version" it would be:


    =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"))* _
    ('Cooling Type'!$B$1:$B$4="T")))



    Without all the whitespace obviously. Just trying to make it legible.

    Neatness aside, that works right?
    As far as I can see, yes that should be the same.

    Quote Originally Posted by Saladsamurai
    P.S. Why did you use the 2nd 'ISNUMBER' function? How does that work? It appears that
    you are testing to see if there is a number, but there is not....there is a "C" or a "K"....
    True, C and K are not numbers, but when you MATCH a range against an array, an arry of numbers or lettes, you get a returned array of numbers (where a match is found), or #N/A! (where no match is found), and ISNUMBER is used to check for matches.

    Quote Originally Posted by Saladsamurai
    P.P.S. I presume this was a typo right?

    =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"))

    Looks like it, I don't know how that crept in. I probably tried in in Excel 2007!
    ____________________________________________
    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. #25
    Sweet Jesus! Thanks again xld! I feel like I really learned something here...not just copy and pasting.

    Still have a lot of questions about SUMPRODUCT, very basic ones.

    Perhaps I will start a new thread later and list them. They are quite fundamental, so I will read the link you gave me in an attempt to answer them myself.

    Thanks again!

  6. #26
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If they are SP specific, please address them to thsi specialist sub-foruum.
    ____________________________________________
    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
  •