Excel 2002 : How to sum up items by month, date and year ?

[Mr. Low]

Dear Sir,

I have the following data sorted by date, may I know what formula must I use
to sum up items the belong to a similar period say Jun-06, Jul-06, Aug-06 at
column D ?

A B C D
1 Invoice Date Amount Sub Total
2 XXXXX 21/7/06 254.00
3 XXXXX 28/7/06 325.00 579.00
4 XXXXX 21/8/06 145.00
5 XXXXX 26/8/06 321.00 466.00
6 XXXXX 21/9/06 144.00
7 XXXXX 23/9/06 121.00 265.00
8 XXXXX 10/10/2006 321.00
9 XXXXX 16/10/06 147.00 468.00
10 XXXXX 11/11/2006 100.00
11 XXXXX 13/11/06 287.00 387.00
12 XXXXX 9/12/2006 988.00
13 XXXXX 16/12/06 125.00 1,113.00
TOTAL 3,278.00 3,278.00

Can you please show me the formulas if other data set is to be sum up by
date as well as by year ?

E.g. 1

A B C D ( Sub Total by date)
Xxxxx 9/12/06 xxxxx
Xxxxx 9/12/06 xxxxx xxxxxx

Xxxxx 12/12/06 xxxxx
Xxxxx 12/12/06 xxxxx xxxxxxxx


E.g. 2
A B C D ( Sub Total by year)
Xxxxx 9/12/04 xxxxx
Xxxxx 9/12/04 xxxxx xxxxxx

Xxxxx 12/12/05 xxxxx
Xxxxx 12/12/05 xxxxx xxxxxx



Thanks

Low

 

[Dave Peterson]

I think you have a few options.

But first, your date column looks kind of funny. If all those cells in that
column have the same number format, then some of those items are not dates.

I'd do this first.

Select column B
Format|Cells|Number tab
Custom category
mmmm dd, yyyy

This is an unambiguous date format. You'll know that 01/02/03 really means (jan
2, 2003 or Feb 1, 2003 or...).

If all your looks correct after this change, then that's good. If some of your
data doesn't look good, you'll want to fix it before you continue.

After that you have a few choices.

If you're not interested in the actual day (just month/year), you could format
column B as mmm-yy and then sort by that column.

Then apply data|subtotals to that column.

If you do have to keep the day, you could insert another column and use:
=b2
and give that helper column that mmm-yy format
and use that column for the subtotalling.

======
If you need to do another subtotal based on year, you could use another helper
column and use =B2 and format that as YYYY and use that for a secondary
subtotal.

=======
And one more option is to learn about data|pivottable and do a summary report
based on that date. Then group that date field by month and year.

 

[Mr. Low]

Hello Dave,

I inseted a column C at the right hand side of the date column, used Format
Cell to get the month of each date and at cell E2 entered formula
=IF(C2<>C3,SUMIF(C$2:C$13,C2,D$2$13),""), copied this formula downwards up
to E13, I got the answer correct.

But If i enter the formula at cell E2 as =
IF(MONTH(B2)<>MONTH(B3),SUMIF(B$2:B$13,MONTH(B2),D$2$13),""), it does not
add up correctly. May I knows whats wrong with this formula ? Can I do the
summing without an additional step of adding a month column as illustrated
above ? Could you show me the formula if this short cut is possible ?

Thanks

Low

 

[Dave Peterson]

I'd try:

=if(month(b2)=month(b3),"",sumproduct(--(month(b$2:b$13)=month(b2)),d$2$13))

And if you have any empty cells in B2:b13, month() will return 1 (January).

=IF(MONTH(B2)=MONTH(B3),"",
SUMPRODUCT(--(MONTH(B$2:B$13)=MONTH(B2)),
--ISNUMBER(B$2:B$13),D$2$13))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Hello Dave,

I inseted a column C at the right hand side of the date column, used Format
Cell to get the month of each date and at cell E2 entered formula
=IF(C2<>C3,SUMIF(C$2:C$13,C2,D$2$13),""), copied this formula downwards up
to E13, I got the answer correct.

But If i enter the formula at cell E2 as =
IF(MONTH(B2)<>MONTH(B3),SUMIF(B$2:B$13,MONTH(B2),D$2$13),""), it does not
add up correctly. May I knows whats wrong with this formula ? Can I do the
summing without an additional step of adding a month column as illustrated
above ? Could you show me the formula if this short cut is possible ?

Thanks

Low

 

[Mr. Low]

Hello Dave,

The formulas work out very well.

Many thanks

Low