Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Rao Ratan Singh
 
Posts: n/a
Default calculation sales of particular month among months of sale

Hi
I m facing problem with my worksheets which i have to calculate total

sales
in particular month.

sheet 1 have

Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
DD/MM/YY
1.10.2005 10000
2.10.2005 250000
3.10.2005
150000
4.10.2005 1000
1.11.2005
2.11.2005
3.12.2005
4.12.2005
1.1.2005 etc

Sheet 2 have
Quarterly Return

Month
Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST

Dear Bob philip
thanks for response but it is not working. I think theres is date you assume
in text but it is in date format mm.dd.yy.
January
February
March

In Sheet 2 i want to calculate Sale amount for particular month which is

in
sheet 1
How it can be

Thanks and regard


  #2   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default calculation sales of particular month among months of sale

Try this version then

=SUMPRODUCT(--(MONTH(Sheet1!$A$4:$A$200)=MONTH($A2)),Sheet1!B$4: B$200)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rao Ratan Singh" wrote in message
...
Hi
I m facing problem with my worksheets which i have to calculate total

sales
in particular month.

sheet 1 have

Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST
DD/MM/YY
1.10.2005 10000
2.10.2005 250000
3.10.2005
150000
4.10.2005 1000
1.11.2005
2.11.2005
3.12.2005
4.12.2005
1.1.2005 etc

Sheet 2 have
Quarterly Return

Month
Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4%

RST
Dear Bob philip
thanks for response but it is not working. I think theres is date you

assume
in text but it is in date format mm.dd.yy.
January
February
March

In Sheet 2 i want to calculate Sale amount for particular month which is

in
sheet 1
How it can be

Thanks and regard




  #3   Report Post  
Posted to microsoft.public.excel.newusers
flummi
 
Posts: n/a
Default calculation sales of particular month among months of sale

As far as I understand it your problem is this:

You have sheet1 with the following

Date sales1 sales2 sales3 for cells say a5:d20

You have sheet 2 with the following: for cells say a5:d??

month <sum of sales1 per month <sum of sales2 per month etc

In A5:A7 enter the months 1, 2, and 3

In B5 enter:
=SUMPRODUCT(--((MONTH(Sheet1!$A$5:$A$20)=$A5)*Sheet1!B$5:B$20))

Copy B5 to C5 and D5
Copy B5:D5 to B6 to D7

In A9 enter Q1
In B9 enter =sum(B5:B7) and copy to C5 and D5

Repeat this for the rest of the months

Hans

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
calculation sales monthwise Rao Ratan Singh New Users to Excel 1 February 1st 06 01:34 PM
Sales V Commission updated calculation Carauto Excel Discussion (Misc queries) 5 December 9th 05 04:11 PM
Month to date sales - reset in new month??? [email protected] Excel Worksheet Functions 2 November 26th 05 09:18 PM
Re-arrangement William Excel Discussion (Misc queries) 4 October 26th 05 09:11 AM
Working days left in the month compared to previous months qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM


All times are GMT +1. The time now is 06:48 AM.

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

About Us

"It's about Microsoft Excel"