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

[Saladsamurai]

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.

[Bob Phillips]

You said it was C or K, but you are only testing for C, so I changed that.

[Saladsamurai]

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

[Bob Phillips]

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.

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.

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!

[Saladsamurai]

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!

[Bob Phillips]

If they are SP specific, please address them to thsi specialist sub-foruum.