CSE Formula / SUMIF and Dates Problem

SubZero · May 13th, 2002, 05:18 AM

I have a spreadsheet consisting of 5 columns.
ColumnA displays dates in a downward fashion. A2=01/10/2001, A2=02/10/2001 etc. The next four columns display sales activity for that day, eg meetings arranged etc. In this column a number is inserted eg 2.
I currently have a totals row at the end of each month but would like to have a seperate table using the countIf or Sumif function. I have tried using the formula (this is not the formula just explaining what i have done!) SUMIF A1:A90 is less than or equal to 31/10/2001 then SUM the meetings in A1:A90 hoping that in that cell I would get the results for october 2001, however excel does not appear to recognise I want all the info for all dates before OCt 31st 2001, any ideas?
I would also have additional cells in the new table to do the same formula but for Nov 1 2001:Nov 30 2001 etc. In this case I would need to conditions, If a2:a90 > NOV 01 2001 but < DEC 1 2001
Thanks Folks

eliW · May 13th, 2002, 06:17 AM

One of the possibilities is to Autofilter by date, using custom filter in which you put your starting and ending dates.
Then use the SUBTOTAL formula to count or sum every column you want in the filtered time range.
Eli

Aladin Akyurek · May 13th, 2002, 06:44 AM

Quote:

On 2002-05-13 04:18, SubZero wrote:
I have a spreadsheet consisting of 5 columns.
ColumnA displays dates in a downward fashion. A2=01/10/2001, A2=02/10/2001 etc. The next four columns display sales activity for that day, eg meetings arranged etc. In this column a number is inserted eg 2.
I currently have a totals row at the end of each month but would like to have a seperate table using the countIf or Sumif function. I have tried using the formula (this is not the formula just explaining what i have done!) SUMIF A1:A90 is less than or equal to 31/10/2001 then SUM the meetings in A1:A90 hoping that in that cell I would get the results for october 2001, however excel does not appear to recognise I want all the info for all dates before OCt 31st 2001, any ideas?
I would also have additional cells in the new table to do the same formula but for Nov 1 2001:Nov 30 2001 etc. In this case I would need to conditions, If a2:a90 > NOV 01 2001 but < DEC 1 2001
Thanks Folks

In G2 enter and copy down as far as needed:

=MONTH(A2)

Enter from I2 on the month numbers 1 to 12.

In H2 enter and copy down:

=SUMIF($G$2:$G$100,I2,$B$2:$B$100)

SubZero · May 13th, 2002, 06:57 AM

Thanks guys, both suggestions work well, But wanting the simplest way of entering as least data as possible the Month function works well, if combined with the year function it will work indefinately... Obviously as aladin suggested I will have to manually enter the values that the formula creates as excel won't sum a formula but i can make a macro to copy the formula and paste values on cell entry, unless there is another way? ideally i didn't want to use VB or macros, and having additional formulas i would have to. Thats why I was hoping it could be done in one formula? Cheers for the help so far guys.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 05:18 AM	#1
SubZero New Member Join Date: May 2002 Posts: 8	I have a spreadsheet consisting of 5 columns. ColumnA displays dates in a downward fashion. A2=01/10/2001, A2=02/10/2001 etc. The next four columns display sales activity for that day, eg meetings arranged etc. In this column a number is inserted eg 2. I currently have a totals row at the end of each month but would like to have a seperate table using the countIf or Sumif function. I have tried using the formula (this is not the formula just explaining what i have done!) SUMIF A1:A90 is less than or equal to 31/10/2001 then SUM the meetings in A1:A90 hoping that in that cell I would get the results for october 2001, however excel does not appear to recognise I want all the info for all dates before OCt 31st 2001, any ideas? I would also have additional cells in the new table to do the same formula but for Nov 1 2001:Nov 30 2001 etc. In this case I would need to conditions, If a2:a90 > NOV 01 2001 but < DEC 1 2001 Thanks Folks

May 13th, 2002, 06:17 AM	#2
eliW Board Regular Join Date: Mar 2002 Posts: 1,805	One of the possibilities is to Autofilter by date, using custom filter in which you put your starting and ending dates. Then use the SUBTOTAL formula to count or sum every column you want in the filtered time range. Eli

May 13th, 2002, 06:57 AM	#4
SubZero New Member Join Date: May 2002 Posts: 8	Thanks guys, both suggestions work well, But wanting the simplest way of entering as least data as possible the Month function works well, if combined with the year function it will work indefinately... Obviously as aladin suggested I will have to manually enter the values that the formula creates as excel won't sum a formula but i can make a macro to copy the formula and paste values on cell entry, unless there is another way? ideally i didn't want to use VB or macros, and having additional formulas i would have to. Thats why I was hoping it could be done in one formula? Cheers for the help so far guys.