SUMIF > Criteria in another Cell

[Frank & Pam Hayes]

Hello,

I have been googling this question for the last hour and have not come up
with an answer.

I am trying to use the SUMIF command in Excel 2000 in Windows 2000. I am
using named ranges for both the range (MyRange) and the sum_range
(Total_Value). I would like the criteria to be based upon a comparison to
the value in another cell (D2).

If I use SUMIF(MyRange,D2,Total_Value) it works fine, howerver, when I try
to make the criteria conditional using < or >, the formula no longer works.

I have tried this as both SUMIF(MyRange,<D2,Total_Value) and as
SUMIF(MyRange,"<D2",Total_Value).


Any advice?

Frank

 

[Frank Kabel]

Hi

try the following:
=SUMIF(MyRange,"<" & D2,Total_Value)

HTH
Frank

 

[Harald Staff]

Hi Frank

That one's not too intuitive. Try
=SUMIF(MyRange,"<"&D2,Total_Value)

 

[Frank & Pam Hayes]

The fix worked perfectly. Thank you both for the help.

Frank

 

[Relix]

Frank.
Try something like:
Naming D2 as mytest

If(MyRange < mytest, SUMIF
MyRange,"<D2",Total_Value),"myrange > mytest")

Felix