sum columns between certain dates
in [General]
|
Prev: Copy a cell from a diferent tab when the tab name is in a cell
Next: referencing multiple cells when data is organised differently
From: lexusnexus on 29 Mar 2010 07:29 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
From: Bernard Liengme on 29 Mar 2010 09:07
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "lexusnexus" <lexusnexus(a)discussions.microsoft.com> wrote in message news:5EDEF8BC-9A5C-40D7-8D2D-4A7AB2AE3C87(a)microsoft.com... > 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 > |