sum columns between certain dates

L

lexusnexus

I have a spreadsheet with headings Jan, Date Received, Feb, Date Received,
Mar, Date Received etc up to Dec.

An amount due in Jan is entered into the Jan column but date received could
be Jan, Feb or Mar. I need to add all amounts received in Jan which means I
need to look in all of the Date Received columns if the date falls between 1
and 31 Jan.

In the Feb column I need to look at amounts received in Feb and Jan and Mar
and total them. In conclusion I would total the amount due in Jan and show
the amount received in Jan. Any help would be appreciated.

Jan 2010 Date Received Feb 2010 Date Received

£100 02/01/2010 £500 28/01/2010
£7,587.35 06/03/2010 £7,144.68 10/02/2010
£5,847.02 05/02/2010 £4,090.86 02//03/2010
 
B

Bernard Liengme

Without wishing to be rude, this is a poorly designed worksheet
I have your sample data on Sheet1 in A1:D4 but we will pretend it does down
to row 100 and across to column X. I have no blank row after the header
labels.
On Sheet 2 I have
Jan x Feb x Mar
Jan 100 x 7144.68 x 0
Feb 5847.02 x 4090.86 x 0
Mar 7587.35 x 0 x 0
Apr 0 x 0 x 0
May 0 x 0 x 0
Jun 0 x 0 x 0
Jul 0 x 0 x 0
Aug 0 x 0 x 0
Sep 0 x 0 x 0
Oct 0 x 0 x 0
Nov 0 x 0 x 0
Dec 0 x 0 x 0

where x denotes an empty cell
The formula in B2 is
=SUMPRODUCT(--(MONTH(Sheet1!B$2:B$20)=ROW()-1),Sheet1!A$2:A$20)
This is copied down the row 13 (Dec)
The range B2:C13 is selected (column with formulas and an empty column); the
range is dragged to column Y
In Y2 enter =SUM(B2:X2) and copy down the column
This give your required results - get my email from my website and email me
asking for file MonthColumns
best wishes
 

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