sumif; conditional sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get a conditional sum based on the relation
of the cells in the column to be evaluated to a fixed
cell. If the amount in the cell in the evaluated column is
greater than or equal to the amount in the fixed cell, the
corresponding amount in the column to be summed will be
included; if the amount in the cell in the evaluated
column is less than the amount in the fixed cell, the
corresponding amount in column to be summed will not be
included. I can make this work if I type the "sumif"
formula using the amount (i.e, 100000) from the fixed
cell, but I can't make it work if I type the formula using
the cell reference (i.e., $s$1). Any help?
 
Hi
if your values are in col. A and your fixed cell is B1 try
=SUMIF(A1:A1000,">=" &$B$1)
 
=SUM(IF((RANGE!$A$1:$A$500>=$S$1),RANGE!$A$1:$A$500,0)))

where RANGE!$A$1:$A$500 is the range of numbers you are looking at, an
$S$1 is the fixed conditional amount you are comparing them to.
Alternately, you could use

=SUMPRODUCT((RANGE!$A$1:$A$500>=$S$1)*(RANGE!$A$1:$A$500))

if there are no empty cells in that range.

Hope that helps
 
Back
Top