If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sum columns between certain dates
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 |
#2
|
|||
|
|||
sum columns between certain dates
Without wishing to be rude, this is a poorly designed worksheet
I have your sample data on Sheet1 in A14 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|