If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumif; conditional sum
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? |
#2
|
|||
|
|||
sumif; conditional sum
Hi
if your values are in col. A and your fixed cell is B1 try =SUMIF(A1:A1000,"=" &$B$1) -----Original Message----- 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? . |
#3
|
|||
|
|||
sumif; conditional sum
=SUM(IF((RANGE!$A$1:$A$500=$S$1),RANGE!$A$1:$A$50 0,0)))
where RANGE!$A$1:$A$500 is the range of numbers you are looking at, and $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. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
sumif; conditional sum
That's it! Thank you!
-----Original Message----- Hi if your values are in col. A and your fixed cell is B1 try =SUMIF(A1:A1000,"=" &$B$1) -----Original Message----- 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? . . |
Thread Tools | |
Display Modes | |
|
|