how to use sumif for cumulative amount?

[Kaspars]

Hi everybody!

Can someone help me with this kind of problem:

I have a template in which I would like to use sumif function (that's my guess that sumif would be best here), but the problem is that I have only four cells to be modified before the template is made. The main idea is to show rate - actual numbers vs. planned numbers in cumulative way - by adding summed amount of next day, then next and so on. By using helper columns this isn't a problem, but as I don't have this luxury, I need to generate only two formulas (one for the first date and another for second date) and after the template has been fulfilled, I can drag down the second formula for all other dates.
As I understand, sumif function doesn't allow to use as criteria an array.
I assume that my explanation isn't quite clear, so I attached file in which You can see the main goal - I need to generate formula for the one blank green marked cell in such a way that I can drag it down along with the cell on the left and the left one generates further dates and right one generates the rate of cumulative actual amount vs. cumulative planned amount. Grey marked cells on the right shows the result if there is possibility to use helper columns.

Thanks in advance!

 

[Faseeh]

@Kaspars

Try this: Drag right and down:

=SUMPRODUCT(($B$3:$B$22>=$G2)*($B$3:$B$22<$G3)*(C$3:C$22))

 

[Kaspars]

Thanks Faseeh, but this doesn't make the trick. My previous description must have been unclear - in the green marked cells I want the sum of actual amount for dates 14/07, then in next 14/07-15/07, then in next 14/07-16/07 and so on divided by planned amount sum for respective dates.

Thanks!

 

[Faseeh]

Ok,

Try this:

=SUMPRODUCT(($B$3:$B$22>=$G$2)*($B$3:$B$22<$G3)*(C$3:C$22))

 

[Kaspars]

Brilliant, that one "$" does the trick!!!

Thanks, have a good day!

 

[Faseeh]

You are welcome!