#### Calculation based with Range of Date

```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
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

```
 0
1/24/2006 6:39:02 AM
excel.newusers 15348 articles. 2 followers.

1 Replies
462 Views

Similar Articles

[PageSpeed] 19

```B2: =SUMPRODUCT(--(TEXT(Sheet1!\$A\$2:\$A\$200,"mmmm")=\$A2),Sheet!B\$2:B\$200)

copy down and across

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message
news:A030FEC7-E1D9-4342-A8EF-911777A44498@microsoft.com...
> 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
> 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
>

```
 0
bob.phillips1 (6510)
1/24/2006 9:05:13 AM

Similar Artilces:

Is there a way to empty the Deleted Items folder by date?
Is there a way to get Outlook to delete items in the Deleted Items folder that have been in there for some period of time (1 week, 1 month, ???)? I know there is a Tools option to empty it on exit, but I sometimes want to recover a file a few days later. FivePoundBag wrote: > Is there a way to get Outlook to delete items in the Deleted Items > folder that have been in there for some period of time (1 week, 1 > month, ???)? > > I know there is a Tools option to empty it on exit, but I sometimes > want to recover a file a few days later. Use AutoArchive. Enable the global...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Form Based Auth.
Hi All, I have checked the option to use form based authentication but I don't get the web page login - still the pop up login box. I have restarted IIS. This is all internal at the moment till i get the forms working. Any ideas. Regards Joe. Do you have "require SSL" checked on the Exchange (and Public) virtual directories in IIS? Have you tried going to the https:// site manually? Forms-based auth only works with SSL. If you haven't required SSL and you go to the http page, it will still work just fine and will pop up the login box. -- Ben Winzenz Exchange MVP ...

sorting dates in Excel 97
Running a worksheet that lists all medical expenses and am not able to always insert date, expense, who to, in correct sequence. Some postings just come in on time. Is there a sort formula that will allow me to sort these entries in sequence? Will the sorting move up the entire row that lists date, amount, payable to, etc. also? Thank you, Steve T. I wouldn't use any formulas for this. I'd select the range (all the columns in that range, plus the last row of headers, plus all the data) and do Data|Sort If you include all the columns that should move with the sort, you'll ...

dates in excel #2
hi all need help with the date function this is the situation i use excel daily to create a report. as this report is printed out i just save changes made. except at the weekend when i also make backups so that my boss can send the report by email on monday mornings. in the report we use the NOW function to display the date. when we used to open the backups the date never used to change ie if saturdays backup was opened on monday morning it used to still display saturdays date. now for some reason the date is automatically updating. anybody know how to prevent this happening You probabl...

How to convert US date format to European
My computer is set up to use the European style dates (ddmmyyyy), not the USA-style dates (mmddyyyy format). So if I enter "01/06/05" I get a date of 01-June-2005. However when I download some information in CSV format at times that same date is formatted like this: "06/01/05". However this is interpreted as 06-January-2005 in the spreadsheet. How can I convert the US formatted values to European, in effect swapping the first two numbers? Thanks T Meisner -- 1025 Osborne Rd., New Germany, NS, Canada B0R 1E0 Rename the .csv file to .txt File|open that .txt file...

Publisher 2007: How do I change dates on calendar page in newslet
I'm creating a newsletter. I added a calendar page. In previous versions of Publisher, I was able to get to Calendar Options to change the month. In Publisher 2007 when I insert a calendar page into a newsletter, I can no longer access the Calendar Options task pane. Please describe how to change the dates. The calendar wizard is broken. Hopefully it will be fixed in a future service pack. Meanwhile you will have to manually change the calendar dates. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Stephanie" &...

Moving Range Problem
Hi all, I'm trying to build a 6 Sigma control chart into an excel workbook. In order to do this i need to be able to work out a formula for the moving range. I need the following : Eg Data sample : 210 , 425 , 600 , 550 , 450 , 470 The moving range is the distance from second to the first, third to the second etc eg A B C D E F Data Sample 210 , 425 , 600 , 550 , 450 , 470 Moving Range 215, 175 , 50 , 100 , 20 The problem I'm having is trying to get excel to work it out. If say 210 was in A1 i ...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Date format of 0 and if statements
I have an is statment as such: =IF('Raw Data'!A5=0,,'Raw Data'!A5) This issue I have is when I format fields as "date" and the reference cell = 0 excel formates the cell as 01/00/00. I have also used the formula: =IF('Raw Data'!A5=0,"",'Raw Data'!A5) The issue I have is I want to create graphs of this data in a dynamic fashion, but when the data range of the graph encounters either "" or 1/00/00 as a date, it plots it as a data point. I need a way to make my IF statment to produce a BLANK or EMPTY cell. Is there a key word in...

2 Formatting Questions
Date Fields: Can we, in the CRM forms, format dates to read into the “15 Aug 2005” format? This is possible in reports, but by default, the forms use only numbers for dates, but in any format. Currency: Any ideas of how to track multiple currencies in the same application - specifically USD and Canadian? I know a Microsoft CRM installation currently supports one language. But aside from using a picklist to specify "USD", "CAN", "MEX", or other I'm concerned about how their forecasting. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.c...

Range in Bar Graphs
Hi you guys, I'm making charts for an assignment due tomorrow and I have to add th range of the values to every bar in my bar graphs (bars showing th average value). It's supposed to be a line just like the one line that indicates th average deviation, yet I don't want this line to show the deviation bu rather the range of values that were the source for the average that i being shown by the bar. So for example we have the values 3, 4, and 5. The Average of tha should be 4. The bar shows the value four (4 is on the y axis) and drawn across thi bard I would like to have a vertica...

Hide Picklist based on value of other picklist.
On the accounts form i have made a new picklist named Partner. In the picklist Customertype i have made a new entry called partner. When someone opens the form i want the picklist named partner to be hidden. When someone selects Partner in the Customertype picklist i want the picklist partner to be visible. If an other value is selected the fields needs to stay hidden. I know i have to make some code on the onchange of the field customertype and i need to make code on the onload of the page. I have tried multiple codes but i cant get it to work. Can someone please help me out please. Hi,...

VBA date question
Why isn't this working? I have a form which has a text box for an attribute of type DATE. This attribute, foo, is normally null when the form is loaded. I tried setting the default value for the text box to "Date()" but it doesn't show up (i'm thinking it's because I'm actually running a join query to generate the record for the form so the form never gets the opportunity to display the default value for foo since it thinks it's supposed to be null, the result from the query) So I added some code like this: Private Sub Form_Open(Cancel As Integer) If (I...

Date/Time field calculation
Hello, i am trying to make a sum calculation on a field that keeps duration of time. How can i do this? I get errors or zero as result. Thanks On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote: > Hello, > i am trying to make a sum calculation on a field that keeps duration of > time. How can i do this? I get errors or zero as result. > Thanks You need to do the calculation at a level at least 1 level below what you want the answer to be in. Example msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00 PM#) will give an ans...

Conditional Formatting
Hello all, I have a spreadsheet with 79,000 cells of information in rows and columns. On a separate worksheet I have 5,000 cells of information. I would like any information in the first sheet that is in the second sheet to turn to red. For example. Sheet1, column A hellothere howareyou iamfine Sheet2, column A hellothere iamfine I would like the two strings in sheet one which appear in sheet 2 to turn red, leaving the middle string black. Is there a way to do a lookup to compare the cell value to a range using conditional formatting? Thanks for any help. Mxx -- murphyz -----...

How do I use text in a cell as a range name in a formula
If cell A1 had the text TEST in it and TEST is the name I have given to a group of cells using the name box what formula can I use to give me the sum of TEST, thats is the sum of the cells in the group called TEST. I understand that I can simply have =SUM(TEST), but I want the formula to refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously Any help appreciated Thank you In this case, you want to use the INDIRECT function. E.g., =SUM(INDIRECT(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kiwi" &...

linking a date colmn with outlook calender
I am using a financial sheet, on which loan is given to customer(s) fo 6 months (180 days), when the 6 months competted, some time fogot/,miss to collect the loan back from customer(s). So, i want t import the same sheet to outlook calender so it automatically inform m on exact date (after completing 6 months). Can some one help me to sort out. Shakil Ahma -- Message posted from http://www.ExcelForum.com Seems preety complicated. Else plot the due dates in excel and conditionally format the cells t be highlighted in case date is the present day i.e today -- Message posted from http://ww...

Change Directory Creation/Modification Date/Time programatically
Hi, Can anyone help me out to change the directory creation/modification date/time through C++/MFC code? Can this be acheived without changing the system time? Thanks in advance. Vignesh. In article <98EDAE87-3F31-4F06-AE8C-378CF91A6F3A@microsoft.com>, Vignesh@discussions.microsoft.com says... > Hi, > Can anyone help me out to change the directory creation/modification > date/time through C++/MFC code? SetFileTime. > Can this be acheived without changing the system time? Yes. -- Later, Jerry. The universe is a figment of its own imagination. "Je...

Data Validation range
Hi, in my book i have a cell with data validation set up to collect data from a range B5:B1005. every month, this range is empty and gets filled up throughout the month. my problem is as i have set the validation range to look at the range B5:B1005, i get a massive dropdown box which ( when i have only 20 items ), is alot of space. can the validation range be sort of active so if there is only 20 items in the range, it only lists the 20 items without the other 800 spaces from the empty cell? i thought maybe a named range but i cannot get it to do what i need. any ideas greatly apprecia...

How to validate User ID creation date in Windows XP
Is there a way to validate the creation date of local User IDs in Windows XP/2003? m_bk wrote: > Is there a way to validate the creation date of local User IDs in Windows > XP/2003? You could query the Security Log but you will only get the information if the log was setup to record events 624 and providing that the log wasn't purged or overwritten: cscript c:\windows\system32\eventquery.vbs /L security /FI "id eq 624" Other than that verifying the creation date on the user's profile folder is the only way that I know of to get the information. ...

Date and Auto Fill
I'm trying to auto fill a column with dates. Everytime I I use the auto fill command it changes the year rather than the day. I do have "day" selected in the fill series command. What am I doing wrong?? Sounds as though you're talking about Excel, not Access, the database product that's part of Office Professional, and the focus of this particular newsgroup. You'd be better off reposting to a newsgroup related to Excel. When you repost, it would probably help the responder to know what version of Excel you're using, and possibly the operating system on ...

Entourage Reminders showing dates for next month overdue
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: pop Recently my reminder window has been showing me events as overdue, but upon looking closer find the date showing as next months events. Why are late December dates showing as overdue in November. The are events that occur monthly and are set up to remind 2 to 3 days in advance. On Nov 26, 5:24=A0pm, tankmas...@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power = PC Email Client: pop Recently my reminder window has been showing me events= a...

Help with a range formula
Hello, In my spreadsheet I have a percent listed in C20 (206%), this is the salespersons percent to quota. Below I have a chart that breaks down the salespersons payout based on that percent Sales Bonus Schedule: Percent to Quota Bonus N1 10-19% N2 \$125 O1 20-39% O2 \$500 P1 40-99% P2 \$1,500 Q1 100-199% Q2 \$2,000 R1 200-400% R2 \$5,000 S1 401% and above S2 \$10,000 In D20 I would like to return the amount of the bonus based on the percen...

Dates
Hi All ... I'm a newbeee using vb2008 express, vs2008, & sql 2008 express. I have created a column in my sql table with the DATE data type as I only want to store/display the date portion in my winform control field which works fine on a but on a report I also get the default time (12:00:00 AM) displayed. How do I truncate or format this to only display the date portion on the report ? Thanks Paul J. Hi, > How do I truncate or format this to only display the date portion on the > report ? The report is done using ? Programmatically you would call ...