Using SUMIF to add data between a range of dates

J

Jaspa

Hi, I am developing a cashflow spreadsheet, and need to add a range of values
(in column B) based on the criteria that they are relating to a set week, ie
in column B has the amount to be paid, and column C has the date the amount
is due. I need to find out the total amount due between 2 dates. Does anyone
know how I can do this?
 
M

Ms-Exl-Learner

I assume that the data will look like this…

A B C
NAME AMOUNT DUE DATE
TWX 6000 20/01/2009
QRS 40000 20/03/2009
ABC 69000 20/05/2009
MNO 80000 20/06/2009
XYZ 2000 20/07/2009
ABC 1000 20/10/2009
EFG 8000 20/11/2009


In D1 cell paste the below formula…
=SUMPRODUCT((C2:C8>DATE(2009,2,1))*(C2:C8<DATE(2009,8,1))*(B2:B8))
The above formula will get the amount due from the date of 01/02/2009 to
01/08/2009, since the date is mentioned inside the formula itself.

If you want to use the from and to date in different cell then use the below
one.
=SUMPRODUCT((C2:C8>G2)*(C2:C8<H2)*(B2:B8))
In G2 mention the Start Date and in H2 mention the End Date.

Increase the cell reference C2:C8 to your desired Range…

If this post helps, Click Yes!
 
P

Per Jessen

Hi

With start date in B20 and end date in B21 try this:

=SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5)

Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top