Sum of values in rows with matching condition

**_saiko** · 12-19-2011, 06:21 PM

Hi,

I have a financial sheet where a value is assigned to each date:

Please Login or Register  to view this content.

The rows count isn't finite, as new values are continuously added.

I need to calculate the sum of values between date1 and date2 where the flag is set to "1".
What would be the best way to do this with an incell function?
SUMIF perhaps?

Thanks

**MarvinP** · 12-19-2011, 06:55 PM

Hi _saiko and welcome to the forum,

See the attached with a cell formula answer and a Pivot Table to check. Two ways to do this problem.

**_saiko** · 12-20-2011, 08:20 AM

Thanks, SUMIFS is what I was looking for.

Now on to a more complex issue.

Please Login or Register  to view this content.

Flag=0 means there is no partial payment.

I need to extend the SUMIFS formula to calculate the expense (Amount) for the following n months, where the value is divided (paid off) during the n months .
Result should be:

Please Login or Register  to view this content.

The From-To intervals are manually created, so the only thing that should be calculated is the "Amount".
The "From" and "To" intervals are the date1 and date2 from the beginning of this thread.

I was thinking of adding some kind of condition inside the SUMIFS, such as (not working): SUMIFS(Monthly,Date,">="&From,Date,"<="&To,Flag,1,Months,">"&MONTH(From-Date))?

Any help appreciated.

**MarvinP** · 12-20-2011, 11:12 AM

Hi _saiko,

I believe a Pivot Table that is grouped by date and the "number of days" is specified is the way to do this problem. See:
http://www.contextures.com/xlPivot07.html

**_saiko** · 12-20-2011, 06:55 PM

That approach is rather not precise as grouping by days tends to shift the starting/ending point for 1-2 days back and forward.
Perhaps if someone could give a more precise answer how I could use Pivot tables for my scenario...

Meanwhile the smartest thing I've come upon is this:
=SUMPRODUCT(Table1[Monthly];--(Table1[Flag]=1);--(Table1[Months]<>"");--(Table1[Months]>((ABS(YEAR(From)-YEAR(Table1[Date])))*12+ABS(MONTH(From)-MONTH(Table1[Date])))))

Still there are o obviously some major faults in logic as this doesn't give the expected result :\

**MarvinP** · 12-20-2011, 07:04 PM

Perhaps you should create a new column next to your date column and extract the Week Numbers into it. Then use these week numbers in your pivot table. Would that help?

**_saiko** · 12-22-2011, 05:54 AM

Not sure what you mean by week numbers?

Anyhow to make things more clear:

How do you call the "feature" of a credit card where you can gradually/partially pay for something that you just bought?
Say you bought something that costs $300 and you decide you'll pay that with your CC in 3 parts. In the following 3 months you will be charged $100 each month (until the $300 is completely paid).

The 2nd table with from/to/amount fields is supposed to calculate the amount that you are going to be charged on the CC during following months (periods 15. - 14.).

If you have something like this in the 1st table:

Please Login or Register  to view this content.

The 2nd table should look like this:

Please Login or Register  to view this content.

**MarvinP** · 12-22-2011, 10:20 AM

Hi,

It sounds like you want to calculate "Installment Payments" or "Plan Payments". In your problem above it seems you'd need to simply add dates and payment amounts to total what you have purchaced. Or look at: http://office.microsoft.com/en-in/ex...001056286.aspx or http://www.ehow.com/how_5074918_calc...nts-excel.html

Secondly, look at the Excel function of Weeknum() to see how to extract the week number from a date. This was what I was suggesting in my post above.