LinkBack URL
About LinkBacks
Hi,
I need help on how to write a conditional sum for a specific problem. I tried with SUMIF and SUM(IF()), but I can't seem to find the right thing.
Basically, I want to sum cells in a column (A) for which cells in another column (B) are lower than a certain value specified in a given cell.
Exemple: if my columns A and B are:
A B
1) 100 3
2) 210 5
3) 350 1
4) 150 2
5) 300 4
if I want to add all values in A for which cells in B are lower than or equal to 3, it's easy:
sumif(B1:B5, "<=3", A1:A5) will give the correct result 600 (that is, 100+350+150)
However I will need have a non constant criteria: instead of requiring the values in B to be lower than or equal to the constant "3", I would to specify that I want the values lower than or equal to THE VALUE IN CELL B1.
In fact, I would like something like this:
sumif(B1:B5, "<=B1", A1:A5) but of course if does not work because the criteria can only involve constant values and not other cells.
The reason is that I want to then calculate this for each line:
for line 1, find the sum of all values in A for which cells in B are lower than B1
for line 2, find the sum of all values in A for which cells in B are lower than B2
for line 3, find the sum of all values in A for which cells in B are lower than B3, etc.
The expected results, could then we displayed in a column C, which would yield:
A B C
1) 100 3 600
2) 210 5 1110
3) 350 1 350
4) 150 2 500
5) 300 4 900
Do you have an idea on what would be the simplest method?
Of course, I could in principle sort the data in ascending order of the column B and calculate the partial sums in column C: however, the values in column B (and thus, the order) is dependent on other variable parameters so I cannot resort everything everytime the values in B change.
Thanks in advance!
Register To Reply
Originally Posted by WHER
)
Bookmarks