Counting data - 1 last question

Thank you.  Now I want to take the sheet that summarizes 
the individual dates divided into the 3 times groups - and 
total them by month.  How can I do that without having to 
manually choose each range for each month?
0
jmcrae (2)
2/5/2005 4:22:04 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
393 Views

Similar Articles

[PageSpeed] 22

[ Original post in:   http://tinyurl.com/69bp2 ]

Do a similar set-up in a new Sheet3

In Sheet3
-------------
Put in A1:    =Sheet2!A1

Copy over the 3 time bands listed in:
B1:B2  : 700, 1500
C1:C2  : 1500, 2300
D1:D2  : 2300, 700
(these will act only as col labels here)

Put in A3 : 1-Jan-2003
Format as Custom, type: mmm-yy (It'll appear as "Jan-03")
Copy A3 down to A14 to fill for the whole year's worth

Put in B3:

=SUMPRODUCT((MONTH(Sheet2!$A$3:$A$1000)=MONTH($A3))*(YEAR(Sheet2!$A$3:$A$100
0)=2003),Sheet2!B$3:B$1000)

Copy B3 across to D3, fill down to D14

The above will return the monthly summary for the year 2003
from Sheet2

Adapt to suit

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Jo" <jmcrae@brktel.on.ca> wrote in message
news:1bf901c50b9e$d480b430$a401280a@phx.gbl...
> Thank you.  Now I want to take the sheet that summarizes
> the individual dates divided into the 3 times groups - and
> total them by month.  How can I do that without having to
> manually choose each range for each mont


0
demechanik (4694)
2/5/2005 11:42:48 PM
Reply:

Similar Artilces:

HELP: Need macro for excel to get data from server database and use results to populate an Access Database
Hi all, Is there a quick way to do a query on an SQL Server database and have the output put direct into an Access Database? I know how to do it by grabbing the records and then inserting each result one by one, but this takes forever to complete and I am after a quick solution, if it exists. Is anybody able to assist ? is it possible? Thanks in advance for any help! Regards Clint you could link to the SQL Server table(s) from Access, write a SELECT query to pull the records you want, and then turn the query into either an Append query to add the data to a native table in the Acces...

Data counting problem
I have a column of numbers 1 through 3000. Another column has a few numbers scatered throughout this range. I want to have a third column that runs the entire height of 3000 cells with either ones or zeros corresponding to the first column where if the number exists in the second column it gets a one and if it doesn't it gets a zero. Can anybody help me? I've been looking into CountIf stuff, but I can't seem to get the criteria to be any of the numbers in column 2. Thanks in advance. -- GlitchCog ------------------------------------------------------------------------ GlitchCog...

Workflow Question #2
Hello, This is my very first workflow. I found the instructions here on the discussion group, but the email is not going out on our system. I'm not sure what I have missed. Here are the details of how I set up the workflow, General tab Publish as: workflow Available to run: nothing checked Scope: Organization Start when: record is created There is one step at the bottom which reads: Line 1: Wait until 2months before contract end date Line 2: wait untol contract:contract end date < [2 months before contract:contract end date] then: Line 3: Send email to Accounting Line 4: Sen...

entity relationship question
hey all ive created a custom entity called 'Example', 'Example' can have multiple Contacts when i go to the 'Example' entity i see the Contacts sub list which is all good, but i dont see an option/search option to select an existing Contact to add to the 'Example' entity. is this possible? eg: Example - Contact 1 - Contact 2 - Contact 3 (i want to add Contact 3, but it already exists, how do i add it to example sublist without creating the same contact?) cheers On May 14, 4:39 pm, devnull <devn...@discussions.microsoft.com> wrote: > hey all &g...

CRM 1.1 client installation error
When I try to install the CRM client and it asks for the URL address of the CRM server, http://CRM it returns an error that it can't find the server components or something like that? Any ideas how to get passed this? Other users can login fine and I can login using http://crm from a Internet Explorer Thx. Have you tried the ip address of the server instead?? Can that machine ping the server ok?? Hope that helps Ben I tried that, but got the same result. I tried to install on another machine, and I get past this, so it must be something with this person's system. &quo...

count related records
2 tables - 1) tickets and 2) messages. Tickets contains the main data and messages contains all the comments made relating to that ticket. How do I count the number of records (or messages) in the related table? So, I have 1 ticket in the system and 4 comments have been made in that ticket. I want to be able to take that number and append it to another ticket table showing "4" in that field. Appending the count to another ticket table is not the thing to do! Say you append 4 and then add another message. The count is now 5 but you only have 4 in the other table....

Data Validation Window?
Excel 2000 ... I do not use Validation much, but usually have no problem. I used COPY/PASTE SPECIAL "Validation" to get Validation into other cells ... Issue is ... the Validation Message Window is now opening way off location from the PASTE TO cells. Above said ... if I select each cell & set Validation ... Validation Window appears ajacent to cell where Validation was set (perfect)... However, when I use COPY/PASTE SPECIAL Validation ... the Window is appearing to stay closer to the COPY FROM cell than the PASTE TO cell. How do I fix this ... other than selecting eac...

How do you write 2 lines of text in 1 cell rather than use 2 cells
I am using Excel 98 and want to know how to get 2 lines of text in 1 cell rather than space it out over 2 cells. See below for example if you dont get it..... This is what (cell no.1) I mean. (cell no. 2) I want to be (cell no.1) able to do this. (cell no.1) Please help.....thanks Type part, then Alt-Enter, then the rest. -- HTH Bob Phillips "!!!help!!!" <!!!help!!!@discussions.microsoft.com> wrote in message news:33CF64B2-E51F-4E8F-9680-1B495146C134@microsoft.com... > I am using Excel 98 and want to know how to get 2 ...

Counting Problem #4
Hi everyone, My problem is with the COUNTA funciton. I have a formula "=IF(COUNTA(K4:Q4)>0,1,0)", set up so that if at least one cell has letter/value, then formula will return a result of 1. The cells, whic this equation counts, are also formulas as well. (Ex =IF('Protocol!$K4="","",'Protoco !$K4)) I want to be able to count the cells that have data, yet not coun those cells which have a blank result. Is there anyway to do this i Excel? Thanks and Have a great weekend, Pet -- peter_river -----------------------------------------------------------...

AutoArchive Question
When I was using Outlook 2000, I had AutoArchive set to archive daily. When I opened Outlook it would then start archiving. Now, I have Outlook 2003 and I have AutoArchive set to archive daily, but it does not archive until about an hour after I open Outlook. The only difference I can see is that in this version I am using Cached Exchange Mode. I was just wondering if anyone knew why this was happening? Thanks. ...

MSPub. is printing "Side 1 name" on a label, how do I stop this?
MS Publisher 2003 is automatically incerting (when you print the label) "side 1 Performer's Name" on a cassette label. This lable has not been modify and prints perfectly in Pub98. The above statement is not found anywhere on the label itself or in the background. How frustrating! What are you using as a template? Is this text statement already on the template? Is this statement the name of the field in your database? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "audioplus" <audioplus@discussions.mi...

Questions about 'locating'-code
To move to specific record number on my form I use the following borrowed code (snippet): Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "ID_address = " & fldSearch If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark Else MsgBox ("ID-number not found") Exit Sub End If Set rs = Nothing I have a few questions about it: 1. Is there a shorter way of coding to move to a specific record in a form? 2. What does 'set rs = Nothing' actually do? What can happen if you omit it? Thanks, John You can do it in one line by operating directly on ...

WaitForSingleObject Question
I have a program with 2 worker threads and each thread uses WaitForMultipleObjects to either shut down or perform a task. Inside each thread there is a loop started by an event that currently uses Sleep(n) to delay an action by a variable time, n. I would like to replace the Sleep(n) with a WaitForSingleObject so I can stop the process if I need to but also use the timeout feature of the WaitForSingleObject to create the variable delay.... If I assume that both threads are in the middle of the timeout, waiting for an event and I need to shutdown, can I use the same event to shutdown b...

Count cells with data
I receive large databases each month. I have been able to format the data using PROPER. Though when I follow the instuctions from "3 formatting shortcuts" it calls for dragging the fill handle to the end of the new column to show all the converted cells. My databases are hundreds of names long! Is there a way to count the cells with text in them in advance - without scrolling down to the bottom - and then put the range into the =PROPER (range of cells with data) command? -- Thank you kindly You are using a second column to convert to Proper? You can Double-Click the Fill...

Data pulled for Access Report
I just started using an existing Access database and have a report that I need to change the data. I have been able to find the links to some data, but am not sure where an important piece is pulled. Is there a way to see the properties of this report and find the tables, queries, or forms it uses to pull data? Open in design view, click on menu VIEW - Properties and see the Record Source. That tells you where the report data is from. It might be a table or query. If a query then open it in design view and check it's sources. -- KARL DEWEY Build a little - Test a little &qu...

A few MVVM and Entity Framework questions
I've been out of the .NET game for a few years now [pre 2005] and starting a little project using the Entity Framework and WPF. My understanding is that the MVVM pattern is the way to go nowadays, but I've got a few questions. This is a purely made up example, so forgive any UI design quirks, and I'm leaving out a bunch of code just as the INotifyPropertyChanged stuff. 1. Let's say I've got a heavy-ish model class called Report. I'd like to create a Report browser type of viewer. I'd like the browser to have a combobox / listbox / treeview / something ...

newfish's question:HOW TO make a sniffer using winsocket in vc++6.0 ?
Hey all, I need a sample about sniffer .any one could help me? please...~~ What do you mean by "a sniffer"? I have an idea what this kind of program does so perhaps it's more appropriate to ask what you are trying to sniff? Tom "feizx" <feizx@126.com> wrote in message news:uve3p3xjHHA.1776@TK2MSFTNGP05.phx.gbl... > Hey all, > I need a sample about sniffer .any one could help me? please...~~ > WireShark (formerly known as Ethereal) is an open source packet sniffer. Start there. joe On Sat, 5 May 2007 22:01:41 +0800, "feizx" <...

Flip data field from column to column
Say you want to just flip the data fieldin one column into a different column, basically flip the data without sorting any of the values,EX 12 57865 123584 123358 578654 1 example but for hundreds of different variable Thanks in advanc If your numbers start in A1, In B1 enter 1, And in B2 enter 2. Then, select both cells and double click on the "drag handle" (little black square in lower right corner of selection). If there are no empty cells in Column A, you will fill column B with a list of numerically ordered numbe...

M05 Scrambled Portfolio Data
Went to check my portfolio today and was shocked to see that I sustained a $7,000 paper loss, across two different accounts, ALL IN CASH ON HAND! Several "sell" transactions from 2002 and 2003, which since then were correctly recorded as cash deposits to the accounts transmogrified themselves into withdrawals. I edited these transactions to make them "buys" and then reedited them to make them "sells" once again, and they reappeared as deposits (inflows) rather than withdrawals (outflows); I have never made a paper profit of $7,000 quite so fast. Nor did I...

Why won't data hide?
Why does hidden data still appear when a chart is generated from a pivot table? How can I make a series disappear when I hide the column in the pivot chart? Hi, If you hide the column manually it will still be displayed in the chart. Because the chart is liked to the pivot table. If you hide the column via the field filter the information should disappear from the chart. Cheers Andy Margie wrote: > Why does hidden data still appear when a chart is generated from a pivot > table? How can I make a series disappear when I hide the column in the pivot > chart? ...

Have data with multiple records associated to same ID
My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan How many subsets are you likely to have (maximum)? Are the values DLL, CND etc in column C, or are they tagged on to the end of the I...

Data validation for symbols
Help, Does anyone know how to do a validation where in column a:a, only allow alphabet, numbers, brackets not other symbols like coma, hyphen? Can we have more than one validation in the same cell? because I already done one validation in column A cells. Or how to to do it by writing codes? Please advice. Any help would be greatly appreciated. THANKS. --- Message posted from http://www.ExcelForum.com/ Data>Validation>Settings>Allow>List =MyList OR enetr a range of cells that contain your list OR enter the members of the list in the window, each separated by a comma (US)....

Count #5
I have the following table for example Meat Chicken Vegetable is there a way to count in excell as total 3 items thanks =COUNTA(A1:A3) -- Gary''s Student - gsnu200803 "Hassan" wrote: > I have the following table for example > > Meat > > Chicken > > Vegetable > > is there a way to count in excell as total 3 items > > thanks > ...

AutoUpdate doesn't appear to successfully update 2.1.1
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) AutoUpdate says 2.1.1 update is critical, however it doesn't seem to be able to install the update. After choosing to download, entering my password, & quitting other programs it seems to check for updates again. The process repeats until AutoUpdate shuts down. I have looked in "view installed updates" and nothing is recorded. As repeated attempts failed, I downloaded updates 1210 and 1211 from microsoft.com/mac manually. I then tried again to use AutoUpdate to install 2.1.1 and was again unsuccessful. I recently bough...

Data/Validation #2
Many thanks Frank, works perfectly. Kind regards And -- Andy ----------------------------------------------------------------------- AndyR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1520 View this thread: http://www.excelforum.com/showthread.php?threadid=26837 ...