Sum value between dates problem driving me mad!

Good Evening All,
I am really struggling here, have tried, nested IF's including AND's but am
a bit stumped.
I am fairly familiar with Arrays, but cannot seem to combine all.
I have a data table (as detailed below). (Hopefully, this should be
'pastable' into Excel - it works for me)
I simply wish to calculate the expected totals per month.
Any help & assistance with this would be most welcome.
Cheers,
Mathew

Note, earliest Start is 01/04/03
      Start Finish Day Rate  Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003
      01/04/2003  23.00
      01/04/2003 09/07/2003 23.00
      01/04/2003 30/07/2003 23.00
      01/04/2003  23.00
      29/04/2003  23.00
      01/04/2003  23.00
      01/04/2003  23.00
      01/04/2003  23.00
      01/04/2003  23.00
      01/04/2003 09/04/2003 23.00
      01/04/2003  23.00
      01/04/2003  23.00
      30/04/2003  23.00
      29/05/2003 23/06/2003 23.00
      19/05/2003 02/06/2003 23.00
      26/06/2003  23.00
      22/07/2003  23.00
      07/07/2003 21/07/2003 23.00
      01/04/2003 14/04/2003 23.00
      22/04/2003 24/04/2003 23.00
      14/04/2003 28/04/2003 23.00
      01/04/2003  23.00
      01/04/2003  23.00
      14/04/2003 19/04/2003 23.00
      12/05/2003 02/06/2003 23.00
      23/05/2003 08/06/2003 23.00
      12/05/2003 26/05/2003 23.00
      19/05/2003 26/05/2003 23.00
      27/05/2003 24/06/2003 23.00
      19/05/2003 27/05/2003 23.00
      30/06/2003 21/07/2003 23.00
      09/06/2003  23.00
      02/06/2003 09/06/2003 23.00
      31/07/2003 24/08/2003 23.00
      21/07/2003  23.00
      09/07/2003 14/07/2003 23.00
      14/07/2003 21/07/2003 23.00
      07/07/2003 21/07/2003 23.00
      28/07/2003 11/08/2003 23.00


Finish


0
mbp1 (14)
8/19/2003 8:24:45 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1164 Views

Similar Articles

[PageSpeed] 4

have you tried
=sumproduct((month(rngA)=1)*1)
=sumprocuct((month(rngA)=1)*rngC)

"Mathew P Bennett" <mbp1@btinternet.com> wrote in message
news:bhu12d$e4j$1@hercules.btinternet.com...
> Good Evening All,
> I am really struggling here, have tried, nested IF's including AND's but
am
> a bit stumped.
> I am fairly familiar with Arrays, but cannot seem to combine all.
> I have a data table (as detailed below). (Hopefully, this should be
> 'pastable' into Excel - it works for me)
> I simply wish to calculate the expected totals per month.
> Any help & assistance with this would be most welcome.
> Cheers,
> Mathew
>
> Note, earliest Start is 01/04/03
>       Start Finish Day Rate  Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003
>       01/04/2003  23.00
>       01/04/2003 09/07/2003 23.00
>       01/04/2003 30/07/2003 23.00
>       01/04/2003  23.00
>       29/04/2003  23.00
>       01/04/2003  23.00
>       01/04/2003  23.00
>       01/04/2003  23.00
>       01/04/2003  23.00
>       01/04/2003 09/04/2003 23.00
>       01/04/2003  23.00
>       01/04/2003  23.00
>       30/04/2003  23.00
>       29/05/2003 23/06/2003 23.00
>       19/05/2003 02/06/2003 23.00
>       26/06/2003  23.00
>       22/07/2003  23.00
>       07/07/2003 21/07/2003 23.00
>       01/04/2003 14/04/2003 23.00
>       22/04/2003 24/04/2003 23.00
>       14/04/2003 28/04/2003 23.00
>       01/04/2003  23.00
>       01/04/2003  23.00
>       14/04/2003 19/04/2003 23.00
>       12/05/2003 02/06/2003 23.00
>       23/05/2003 08/06/2003 23.00
>       12/05/2003 26/05/2003 23.00
>       19/05/2003 26/05/2003 23.00
>       27/05/2003 24/06/2003 23.00
>       19/05/2003 27/05/2003 23.00
>       30/06/2003 21/07/2003 23.00
>       09/06/2003  23.00
>       02/06/2003 09/06/2003 23.00
>       31/07/2003 24/08/2003 23.00
>       21/07/2003  23.00
>       09/07/2003 14/07/2003 23.00
>       14/07/2003 21/07/2003 23.00
>       07/07/2003 21/07/2003 23.00
>       28/07/2003 11/08/2003 23.00
>
>
> Finish
>
>


0
Don
8/19/2003 9:34:12 PM
Hi Don,
Thank you for this, I have recently discovered sumproduct, and learning it's
usefulness vs arrays.(or many if's)
From Tom Ogilvy's post worksheet.functions 14/08/03 06:31 Ref TotalFinder. -
I think clever, as I encounter this problem.
Mathew
"Don Guillett" <donaldb@281.com> wrote in message
news:uYX6hmpZDHA.3768@tk2msftngp13.phx.gbl...
> have you tried
> =sumproduct((month(rngA)=1)*1)
> =sumprocuct((month(rngA)=1)*rngC)
>
> "Mathew P Bennett" <mbp1@btinternet.com> wrote in message
> news:bhu12d$e4j$1@hercules.btinternet.com...
> > Good Evening All,
> > I am really struggling here, have tried, nested IF's including AND's but
> am
> > a bit stumped.
> > I am fairly familiar with Arrays, but cannot seem to combine all.
> > I have a data table (as detailed below). (Hopefully, this should be
> > 'pastable' into Excel - it works for me)
> > I simply wish to calculate the expected totals per month.
> > Any help & assistance with this would be most welcome.
> > Cheers,
> > Mathew
> >
> > Note, earliest Start is 01/04/03
> >       Start Finish Day Rate  Apr-2003 May-2003 Jun-2003 Jul-2003
Aug-2003
> >       01/04/2003  23.00
> >       01/04/2003 09/07/2003 23.00
> >       01/04/2003 30/07/2003 23.00
> >       01/04/2003  23.00
> >       29/04/2003  23.00
> >       01/04/2003  23.00
> >       01/04/2003  23.00
> >       01/04/2003  23.00
> >       01/04/2003  23.00
> >       01/04/2003 09/04/2003 23.00
> >       01/04/2003  23.00
> >       01/04/2003  23.00
> >       30/04/2003  23.00
> >       29/05/2003 23/06/2003 23.00
> >       19/05/2003 02/06/2003 23.00
> >       26/06/2003  23.00
> >       22/07/2003  23.00
> >       07/07/2003 21/07/2003 23.00
> >       01/04/2003 14/04/2003 23.00
> >       22/04/2003 24/04/2003 23.00
> >       14/04/2003 28/04/2003 23.00
> >       01/04/2003  23.00
> >       01/04/2003  23.00
> >       14/04/2003 19/04/2003 23.00
> >       12/05/2003 02/06/2003 23.00
> >       23/05/2003 08/06/2003 23.00
> >       12/05/2003 26/05/2003 23.00
> >       19/05/2003 26/05/2003 23.00
> >       27/05/2003 24/06/2003 23.00
> >       19/05/2003 27/05/2003 23.00
> >       30/06/2003 21/07/2003 23.00
> >       09/06/2003  23.00
> >       02/06/2003 09/06/2003 23.00
> >       31/07/2003 24/08/2003 23.00
> >       21/07/2003  23.00
> >       09/07/2003 14/07/2003 23.00
> >       14/07/2003 21/07/2003 23.00
> >       07/07/2003 21/07/2003 23.00
> >       28/07/2003 11/08/2003 23.00
> >
> >
> > Finish
> >
> >
>
>


0
mbp1 (14)
8/19/2003 10:02:36 PM
Reply:

Similar Artilces:

Pivot Chart problems
This is a multi-part message in MIME format. ------=_NextPart_000_00B3_01C4E370.755D7D40 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I have two issues with pivot charts. 1) I create a pivot table and chart with VB. I want thick lines on the = chart, so I do as follows: For i =3D 1 To .SeriesCollection.Count .SeriesCollection(i).Select With Selection.Border .Weight =3D xlThick End With Next This works great. However, upon selecting a Page Field value from the =...

Need to Set CD Drive as "R:" can that be done under XP
I have set up a Mini Tower Using XP as a CD Reader/Copier. I Need to Set the first CD Drive as "R:" can that be done under XP TIA Bob in Wisconsin Robert J. Stevens wrote: > I have set up a Mini Tower Using XP as a CD Reader/Copier. > I Need to Set the first CD Drive as "R:" can that be done under XP Now is a great time to point you to one of the easiest ways to find information on problems you may be having and solutions others have found: Search using Google! http://www.google.com/ (How-to: http://www.google.com/intl/en/help/basics.html ) Speci...

Formatting Cells Containing Date Fields That Are Text
Someone here at work imported into Excel a column of text data that looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could simply highlight the columns, click on Format/Cells/Custom and create mmddyyyy. I do this but the data doesn't change UNTIL I highlight each cell and press F2 or double-click in each cell or create a formula using Right, Mid, etc. When I press F2 or double-click, I notice that an X and a check mark both appear to the right of the Row/ Column box. There's got to be an easier way to do this besides 'onesie-twosie' but we can't figure it...

How do I have excel highlight days before & after a date on a cale
How do have excel highlight 15 days prior to a chosen date on the calendar below & 30 days after? 2010 JANUARY FEBRUARY S M T W T F S S M T W T F S 1 2 1 2 3 4 5 6 3 4 5 6 7 8 9 7 8 9 10 11 12 13 10 11 12 13 14 15 16 14 15 16 17 18 19 20 17 18 19 20 21 22 23 21 22 23 24 25 26 27 24 25 26 27 28 29 30 28 31 MARCH APRIL S M T W T F S S M T W T F S 1 2 3 4 5 6 1 2 3 7 8 9 10 11 12 13 4 5 6 7 8 9 10 14 15 16 17 18 19 20 11 12 13 14 15 ...

Problem with the reconnection of a mailbox to a different user
Hello everybody, I have a problem with the reconnection of a mailbox, when I try to reconnect it to a different user. This is the scenario: 1) User A has a mailbox, I disconnect it. 2) Creation of User B 3) Reconnection of mailbox of user A to user B (I have tried not only with recovery center, but also directly through mailbox list section) No errors appears during theese steps, but when I try to create a new profile in outlook (I have created it only in the server, this is a test server), I see that the mailbox of user B (or the user B?) has not found! If I try to use Outlook Web Access, ...

2005 Update Retirement Account Problem
I am having the same problem as discussed in a post on 10/5. Has anyone else seen this or know of a solution? And btw, I am not running XP SP2, just a fully updated SP1 system. I tried everything I could find in the KB to no avail as well. I am getting really frustrated with how many bugs there are in 2005. I have used MS Money for years and have never had as many problems as I have with 2005. I can't believe that this product made it out to the public with as many problems as there are. Any comments would be appreciated. ...

Help needed to run macro
Argh!!! I think I may have created a macro to resolve an earlier problem, but when I try to run it I get... 'Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified. To run the macros, you can either have them signed or change your security level.' How can I get my macros to run on my machine, where they are created, without compromising security? I've found and run selfcert.exe but it hasn't made any difference. Do I need to do anything else to make this effective? I'm a novice at this so step ...

Problem sending emails from Outlook Express Ver6 NEVER MIND!
I am trying to use Outlook Express Ver6 instead of Eudora. I can't seem to send e-mails from it. When I send a test to myself I get the error: The message could not be sent because the server rejected the sender's e-mail address. The sender's e-mail address was (MY ADDRESS) .. Subject 'TEST', Account: 'incoming.verizon.net', Server: 'outgoing.verizon.net', Protocol: SMTP, Server Response: '550 5.7.1 Authentication Required', Port: 25, Secure(SSL): No, Server Error: 550, Error Number: 0x800CCC78 Can anyone suggest what is wrong? Must be something...

Problem sending e-mails with Outlook & SBC Yahoo DSL
I have been unable to fix a problem and am looking for help. I have Outlook 2000. I also have SBC Yahoo DSL. I have spent hours on the phone trying to fix this problem, reloaded my software, etc. I can recieve e-mail from the SBC Yahoo DSL server with my Outlook, but cannot send e- mails. The error message I get is 0x800ccc0f. I know this means the outgoing mail server does not recognize and log me in, but does anyone know a way to change this? SBC made sure my settings were correct, but the problem persists. They claim it is an Outlook problem, but it never happened with other ISP...

Problems importing pst file...
One of the users in our office tried to open Outlook & recieved an error message that indicated that the pst was gone or corrupt. After running the Inbox repair tool to no avail, as well as picking up a copy of their pst from yesterday's backup, I'm still unable to do anything with it! When trying to import it, I get the message "Properties for this information service must be defined prior to use" & after hitting ok, I get a message that the file isn't a personal folders file! I have read several long-winded posts in other forums, which basically have given me no...

CRM Mailbox User on the 3.0 Demo Drive ?
Does anyone know what the CRM System Mailbox User is for the 3.0 demo drive? -Luke ...

Problem of space between items in SMALLICON mode
Hi, I have problem with spacing between items in LVS_SMALLICON list mode. Some that have long text hide the others. Not when i'm changing mode, just when I change data in my list. I use SetWindowLong to change list mode, and tried list.Arrange, list.redraw and list.update functions with no success. Actually, list doesn't change space between items when I change data in my list. I browsed the group, and found some unanswered subject about the same problem, so if someone could help me it would be cool. TIA. Have you tried the CListCtrl::SetIconSpacing before CListCtrl::Arrange? Vict...

Outlook / Exchange 2003 user connection problem
Ok, we are doing our Outlook configuration in a two step process first we add the Computer to the domain ( not the user ). Then we configure Outlook with the exchange server and user information. All of our users are created and have a default password. Problem is that when we go to setup Outlook we are only able to setup 2 - 5 computers a day. After we get 2 - 5 setup, when we try to install/configue another Outlook client we receive a message that say Cannot connect to Exchange server or Exchange server does not exist. But if we wait a day and go back to setting up Outlook again we are ab...

Portfolio Manager won't show daily change of value for mutual funds
I am still using Money Premium 2006. I would like to know if the following problem (feature?) is observed by other folks, either in M2006 or M2007. The Portfolio Manager has columns "Change" and "TotChng." The former is the change in price, per share, of a security for one day. The latter is, roughly speaking, "Change" multiplied by the number of shares held. I don't remember which of these columns is visible by default. I have chosen to view create of these columns in the "custom" view of my Portfolio Manager. My problem is that for mutual funds, ...

Problem with a MIN function
Hi all Please can someone explain to me why this doesn't work: =MIN((A46:A52>0)*(A46:A52+B46:B52)) array-entered I've got dates in both ranges - and both ranges also contain blanks. I don't understand why this formula does not return what I want!! I'm trying to get the earliest date in A46:A52 where there is a blank in the corresponding cell in B46:B52. I've checked the formula (including the F9 option in the formula bar to view each part) and it's got me beat! -- Andy. Hi try the arraz formula =MIN(IF((A46:A52>0)*(B46:B52=""),A46:A52)) >---...

Assign $true value to a variable and pass to function
Whats wrong with this picture? I want to assign a $false value to a variable and pass that to a function. However the result is always zero even though I know it to be incorrect. A sample result is - "Total Number of Active Mailboxes on EXCHSERVER01 is 0" Now, if I replace HiddenFromAddressListsEnabled -eq $Y with HiddenFromAddressListsEnabled -eq $false, I get the correct answer. Can someone help with this please? FUNCTION E2K7Mailboxes { param ($X, [bool]$Y) $ActiveMBXs = (get-mailbox -filter {(ServerName -eq "$X") -and (HiddenFromAddressLists...

Outlook2007 automation problem with ".Display"
I am using automation in a VB5 program to send Emails using Outlook2007. If I use the ".Send" mailitem the email is sent out Immediately. If I use the ".Display" mailitem in my program, after I look over the Email and then click the "Send" button on the Outlook display screen, the email is placed in my outbox but does not get sent out. I have to exit my program and then click "Outlook". When Outlook displays, I can see the Email in the Outbox which will then automatically go out (without me having to click send and receive). I want th...

Sum Times
I have four fields on a form to show time. I want a seprate "Total" field to add the time between the first two fields and then add the time between the second two fields. Like this: In LunchOut LunchIn Out Total 6:00am 12:00pm 12:30pm 4:30pm 10 hours The first four fields are stored as medium times. Can someone let me know how to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "ladybug via AccessMonster.com&quo...

Select a drive
I am using a "Menu button" that opens macro that only allows me to import from a certain drive. Macro:Transfer type-"Import Delimited", Specification Name-"ALID0001 Import Specification", Table Name-"ALID0001", File Name-"g:\ALID0001.TXT", And Has Field Names-"Yes". This is great until I have use a different drive to import from. I would like the menu button when selected to open explorer and then I would like to select the drive from which the data could be imported from. Thank You, ...

Calender Problem after Exchange Migration
Hi all, I've got a bit of a Exch Calendar problem. We recently migrated from Exch2000 Srv to Exch2003 Srv. We also changed domain names (AD and company) as well. Due to time constraints and other issues I won't go into, things were a little rushed and the way we did the migration was probably not by the MS rules. Basically we restored a backup of the mailboxes from the old Exch2000 server to the new Exch2003 server. This was done via Qinetix MB idata agent. We thought this would be quicker than exporting the mailbox's out as PST files and then importing them. This part app...

Combined Cell Value
Hi All, I have list in following format: A B C D E 0 1 2 3 4 Results wanted: F "0","1","2","3","4" One more question is it possible to merge multiple column values into one column without lost the values? Thanks in advance. While you posted in a programming newsgroup, the solution does not require VB code to accomplish; simply copy/paste the following formula into F1 and copy it down as needed... =""""&A1&""","""&B1&am...

how to create conditional format linked to date
I wish to create a conditional format linked to date, for example if info. in cell is less than 2 months old, the cell is green, if info. is more than 2 months old the cell turns red. one way: Format the cell to green. The Format>Conditional Formating>change cell is to formula is =if(today()-60=a1,true,false) and set the format to red HTH "nicko68" wrote: > I wish to create a conditional format linked to date, for example if info. in > cell is less than 2 months old, the cell is green, if info. is more than 2 > months old the cell turns red. I don't beli...

Autonumber problem
Hi, I have a problem with autonumber field in my project: when I deleted some last records, it seems MS Access saved the last number of the autonumber. So when I added a new record, the number would be skipped from the previous record. I'd like the next record will be in order from the previous record in my outonumber field. Is anybody can tell me how to solve this problem?. Thanks... KT -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 If the value of the autonumber actually matters then you shouldn't be using atuonu...

Lookup values on a form, trivial question?!
Hi everyone, This is really silly but I have searched everywhere (starting with my "Form and report design for Access 2007" book) for an explanation on how I make a form with: Field A (text box) is INPUT value (customer number), and Field B-D should (after input value is entered in "textbox A") display more information about the customer in different textboxes (such as "customer name, phone number etc")- (This is just an example, the "customer-information" could be in one or several different - relational - tables, if that makes a difference...)...

Mac problems opening excel files when connected to two windows shares
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm noticing a strange problem when I try to open excel files when I connect to a second windows server share. I'm getting a cannot open excel file error. Not sitting at my work mac computer at the moment so not quite sure that was the exact error. <br><br>This error is occuring on both leopard and snow leopard with Office 2008 for mac. <br><br>The two windows servers are being connected to by smb://server1/common and smb://server2/common. <br><br>If i connect to each one i...