Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 26th 06, 12:17 PM posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default Calculating monthly totals for current and previous year

Does anyone know the best formula for totalling values in a column that
correspond to each month of the current and previous year?

I have a sales register with the date of sale and commission on each row but
want to display the total commissions for each month of the current year on
one worksheet and monthly totals for previous year on athother worksheet.

I have tried the following formulas but keep getting a popup box saying that
the formula contains an error:

=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)

=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())-1),Websites!R5:R31)

Any ideas would be greatly appreciated.

Thanks
Simon



  #2   Report Post  
Old February 26th 06, 12:43 PM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default Calculating monthly totals for current and previous year

One way ..

In A2: 01-Dec-2005
In B2: 31-Dec-2005

Then in say, C2:
=SUMPRODUCT((Websites!$B$5:$B$31=A2)*(Websites!$B $5:$B$31<=B2),Websites!$R$
5:$R$31)
will return the total commission for Dec 2005

C2 can be copied down to calc accordingly
for other month / year stipulated in cols A and B (in A3:B3, A4:B4, etc)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pieman" wrote in message
...
Does anyone know the best formula for totalling values in a column that
correspond to each month of the current and previous year?

I have a sales register with the date of sale and commission on each row

but
want to display the total commissions for each month of the current year

on
one worksheet and monthly totals for previous year on athother worksheet.

I have tried the following formulas but keep getting a popup box saying

that
the formula contains an error:


=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())),Websites!R5:R31)


=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())-1),Websites!R5:R31)

Any ideas would be greatly appreciated.

Thanks
Simon






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating monthly totals Pieman Excel Worksheet Functions 10 February 26th 06 04:55 PM
years change to current year nwg Excel Worksheet Functions 5 January 1st 06 03:29 PM
Previous year data into chart dietmarhannam Excel Discussion (Misc queries) 0 November 25th 05 01:13 PM
How do I sum YTD totals based on monthly totals Bsgrad02 Excel Discussion (Misc queries) 3 July 12th 05 04:59 PM
How do I forecast monthly and annual totals from previous year's . Jeff Hart Excel Worksheet Functions 0 April 1st 05 07:19 PM


All times are GMT +1. The time now is 09:16 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017