Sum column based on date

goldcat

Board Regular
Joined
Jun 26, 2002
Messages
160
Hi all,

For this I am only worried about two columns E(date) & F(amount).

What I need to do do is sum the values in column F that are before today which I have placed in A1. I thought I could use a sumif but I can not workout the arguments to use.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Maybe this:

=SUMPRODUCT(--(E2:E200 < A1), F2:F200)

...or this:

=SUMIF(E2:E200, "<"&A1, F2:F200)
 
Joined
Jul 30, 2006
Messages
3,656
goldcat,

Excel Workbook
ABCDEF
13/22/20096600DateAmount
23/1/2009100
33/3/2009200
43/5/2009300
53/7/2009400
63/9/2009500
73/11/2009600
83/13/2009700
93/15/2009800
103/17/2009900
113/19/20091000
123/21/20091100
133/23/20091200
143/25/20091300
153/27/20091400
Sheet1



Have a great day,
Stan
 

goldcat

Board Regular
Joined
Jun 26, 2002
Messages
160
Thanks for the quick replies I ended up using

=SUMIF(E2:E200, "<"&A1, F2:F200)

It worked as I knew it should ... I would have never thought of using the ("<"&) combination
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,087
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top