excel formula depending on dynamic values in different columns

Question

I am trying to create an excel formula using SUM and SUMIF but cannot find how to.

I have a first column(A) which is the total time of a piece of work and then for each row the time spent in that task during each day(columns B, C, ...).

For each day(columns B, C, ...), the formula would return the sum of only those values in column A that(for that specific column), relate to task that have been completed that day: the sum of all cells within a row is equals or more than the time the task was allocated.

Example for one 12-hours task:

    A   B  C  D  E    
    12  4  6  2  0

Using the formula:

    A   B  C  D  E
    12  4  6  2  0    
    0   0  0  12 0

where 12 is displayed in column D because 4 + 6 + 2 = 12(Column A)

Second example(3 tasks):

    A   B  C  D  E    
    10  9  0  1  0    
    21  8  8  5  0    
    5   0  0  3  2

Using the formula:

    A   B  C  D  E    
    10  9  0  1  0    
    21  8  8  5  0
    5   0  0  3  2
    0   0  0  31 5

Where:

31(Day D) = 10(Task 1 is finished that day) + 21(Task 2 is finished that day too)

5(Day E) = Task 3 is finished that day

Tried this formula (for Day B):

 SUMIF(B1:B3,">=A1:A3",A1:A3)

(Sum those values in column A if the cells in that row p to column B(in this case just B) are >= than those iterated).

Then for column C, it would be,

SUMIF(C1:C3 + B1:B3,">=A1:A3",A1:A3)

The above examples did not work(first returns zero, second is an invalid formula), Any ideas?

Thank you.

What would the formula be for column D? Also, what results are you getting with those formulas? — ServerS
Thanks. In the last example, result is zero(=SUMIF(B1:B3,">A1:A3",A1:A3)) for first formula and alert with syntax error for the second. I guess for column D would be SUMIF(D1:D3 + C1:C3 + B1:B3,">A1:A3",A1:A3), basically if all cells in a row are more in value than the first cell of that row add the value of that first cell to the result that is returned by the formula. — kandan
I'm confused by what you are trying to do with the + symbols? — ServerS
I can't figure it out. The closest I've come is this, and it doesn't do what you want, but it might get you on the right path: (Used in column D): IF(SUM(B1:D1)>=SUM(A1),SUM(A1),0)+IF(SUM(B2:D2)>=SUM(A2),SUM(A2),0)+IF(SUM(B3:D3)>=SUM(A3),SUM(A3),0). — ServerS

kandan kandan · Accepted Answer · 2014-08-22T23:58:59

Formula below given by user ServerS works fine:

Col B:

=IF(SUM(B2)=A2,A2,0)+IF(SUM(B3)=A3,A3,0)+IF(SUM(B4)=A4,A4,0)+IF(SUM(B5)=A5,A5,0)

Col C:

=IF(SUM(B2:C2)=A2,A2,0)+IF(SUM(B3:C3)=A3,A3,0)+IF(SUM(B4:C4)=A4,A4,0)+IF(SUM(B5:C5)=A5,A5,0)

Col D

=IF(SUM(B2:D2)=A2,A2,0)+IF(SUM(B3:D3)=A3,A3,0)+IF(SUM(B4:D4)=A4,A4,0)+IF(SUM(B5:D5)=A5,A5,0)

However there are two inconvenients: if new rows are added it needs to be adapted and include another IF(). Would be better to have a generic SUM if IF's Trying to propagate the formula to adjacent cells is not possible as it would change part of the formula like "=A2,A2,0" to "=A3,A3,0" which needs to keep the same.

Any other ideas that improve this, if possible, are appreciated.

excel formula depending on dynamic values in different columns

2 Answers