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

[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!