Sumproduct #2

Hi all!
I am trying to use Sumproduct with dates that come off an SQL databas
and are in the format of 02/12/2003 15:30.

This is forcing me to use the following formula 
=SUMPRODUCT((Data!$E$2:$E$3893>G$3)*(Data!$E$2:$E$3893<G$2)*(Data!$C$2:$C$3893=$A4)*(Data!$G$2:$G$3893))

where $G3 is 02/12/2003 00:01 and $G2 is 02/12/2003 23:59

Is there any way I can just look at the date only rather than the time
as well, so that anyone can enter a date from, and a date to 

In anticipatio

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
0
12/2/2003 2:04:25 PM
excel 39879 articles. 2 followers. Follow

1 Replies
517 Views

Similar Articles

[PageSpeed] 10

Use   SUMPRODUCT((INT(Data!$E$2:$E$3893)>G$3)*

and so on, that will shave off the times from the values
-- 

Regards,

Peo Sjoblom


"augam" <augam.xtc6b@excelforum-nospam.com> wrote in message
news:augam.xtc6b@excelforum-nospam.com...
>
> Hi all!
> I am trying to use Sumproduct with dates that come off an SQL database
> and are in the format of 02/12/2003 15:30.
>
> This is forcing me to use the following formula
>
=SUMPRODUCT((Data!$E$2:$E$3893>G$3)*(Data!$E$2:$E$3893<G$2)*(Data!$C$2:$C$38
93=$A4)*(Data!$G$2:$G$3893))
>
> where $G3 is 02/12/2003 00:01 and $G2 is 02/12/2003 23:59
>
> Is there any way I can just look at the date only rather than the times
> as well, so that anyone can enter a date from, and a date to
>
> In anticipation
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>
> ~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements


0
terre08 (1112)
12/2/2003 5:54:11 PM
Reply:

Similar Artilces:

Country Codes #2
I can't for the life of me find the GP menu item that maintains the codes. Can anyone point me to it? Open the Country Code Maintenance window. (Tools >> Setup >> Company >> Country Codes) The Country Code Maintenance window will be available only if you have marked to enable Intrastat tracking in the Company Setup Options window. See Help for more info -- Elisabeth "Ken Denman" wrote: > I can't for the life of me find the GP menu item that maintains the codes. > Can anyone point me to it? If you don't have Intrastat Tracking turned on ...

Is it possible to... #2
export 1 or more macros automaticlly tather manually? Thanks rpqc...@gmail.com wrote: > export 1 or more macros automaticlly rather then manually? Thanks ...

formatting #2
I have a large worksheet that I need to change the text to all caps. I have tried using the UPPER function but it will only work in one cell. How to I get the entire worksheet to become all caps? Help! Hi see http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany Jackie wrote: > I have a large worksheet that I need to change the text to > all caps. I have tried using the UPPER function but it > will only work in one cell. How to I get the entire > worksheet to become all caps? Help! Hi Jackie If you have trouble using macros you can do this ...

Can't open my money file #2
Good evening, From today i can't open my money file, i had formatted my pc and i reinstalled money as usual. Today when i open my money file after typing the correct password the program show a screen where it say "Money has detected that you did not close file xxx before exiting Money now needs to verify the information in your file. .... Do you want do this now?" I click yes and it say "The email address or password is incorrect. Please try again" But i dont put nowhere any email address this is an old money file converted to new money 2008 un year ago i never ...

File Modified by Another User #2
I have recently upgraded my PC to Windows XP SP2 and am having a curious problem. When I attempt to save the file, I get a dialog box stating the file might have been modified by another user - do I wish to save a copy or overwrite changes. The workbook is not shared - however, it does reside on a Linux Samba share. But then again, it has always resided on the share. Only since the SP2 update, has this started happening: These are the steps I followed: 1. Opened Excel 2. Opened the file on the Samba share 3. Clicked SAVE 4. Got the dialog box asking to overwrite changes or s...

RMS 2.0/Windows Permissions Error
Good Morning Everyone, I have a question for the group. I have a customer running RMS 2.0, pre SP2 on Windows XP. The customer is trying to lock down his cashiers from installing unauthorized software and other such abuses on the computers. Here is the problem. When locked down he is getting the error: "Server Failure 139: A server failure occurred while attempting to read the register." This error only happens as the POS and goes away when the user is added as a local administrator. I am fairly confident this is a problem with permissions regarding licensing or OPOS, bu...

Macro to Protect workbook using a password #2
I know this would be a little unorthodox, but I currently have a macro that will unprotect a workbook including a password. The line is as follows: ActiveWorkbook.Unprotect (password) I also want a macro that will do just the opposite, including the password. Is this possible? I've tried the following with no luck. ActiveWorkbook.Protect (password), Structure:=True, Windows:=True You got two replies when to your identical post on Tuesday. If they weren't suitable, please explain why not. If you didn't see them, you can always see your posts and any replies using the Go...

payment terms #2
In SOP (GP 8.0 SQL 2000MSDE), sometimes an invoice will not show the payment terms and other times it will. The customer being invoiced does contain default payment terms. Has anyone else experience this and does anyone have any suggestions? -- Have a great day! W Sue W Sue, In my experience, I have never seen this functionality not work properly, even going back many versions. So I would suspect one of the following is happening: 1) Customer did not have the terms set up at the time the invoice was being created...changes to customer terms (on the customer card) will not update ...

Help with Calculation #2
Hi, I have the following XLS with 3 sheets: Credits, Inventory & Debits. Credits sheet (Stock sold) columns: 'Product Code', 'Product' (Text field) & 'Quantity' Inventory fields: 'Product' (Text field), 'Company', 'Supplier', 'Product Code', 'Quantity', 'Cost Price' & 'Retail Price'. Debits Sheet (Stock bought) columns: 'Product Code', 'Product' (Text field), 'Quantity' & 'Total cost'. New stock entered as 'Product Code', 'Product', 'Quantity&#...

Importing portfolio to Netscape 7.2
Having trouble viewing portfolio with IE6.0 Updates every minute regardless of update freq settings. Cannot do any work. Therefore...... Is there a way to import my stock portfolio from Microsoft Money online server into Netscape 7.2 "Money & Business" portfolio ? If I have to rebuild it from scratch in Netscape, it would take a month to re-enter all the historical buy and sell activity. If I do not, all Gains & Losses YTD and over-all will be false. Thanks for any help. Jim. ...

2 panel brochure printing in publisher
I am trying to print a 2 panel brochure in publisher and when I print preview everything fits but when it prints pages 1, & 3 get cut off. I would like all the pages to fit and print so the pages are aligned when folded. its an 8x11 normal sheet. I don't know which margins to use to make it print correctly. Please help! Have you setup your brochure as a booklet? Do you know your printers margin limitations? A tutorial is here: http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Mary Sauer MVP http://msauer.mvps.org/ "Dominikin" <Dominikin@d...

Passing 2-D array as function parameter.
If I pass a 2-D array ar[4][6] like this: foo(int ar[][6]...); What has been passed? Is it a pointer of whole array? Thanks. if you want to point to whole array you must call foo(ar) and in prototype or definition write foo (int ar[] [6]) and if you want to accsess only one row you must write foo(ar[4]) at calling the function "Frank E Rogers" <syang@pelco.com> wrote in message news:#u9vtA0UEHA.212@TK2MSFTNGP12.phx.gbl... > If I pass a 2-D array ar[4][6] like this: > foo(int ar[][6]...); > What has been passed? Is it a pointer of whole array? > Thanks. > &...

Chart
Hello, I created a line column chart on 2 axis. I have four data sets. The first 3 should go on the primary Y axis as columns and the 4th should go on the secondary Y axis as a line. However, Excel wants to automatically put both the 3rd and 4th data sets on the secondary axis as lines. I cannot find any command to change the 3rd data set to go onto the primary axis as a column. Any suggestions would be most appreciated! -Patty On Tue, 11 Nov 2003 13:27:33 -0800, Patty = <anonymous@discussions.microsoft.com> wrote: > Hello, > > I created a line column chart on 2 ...

subtotals #2
Hello, I have a list of dollar amounts corresponding with days in the months. I am trying to create a subtotal for each month and not each day of the month. When I do subtotal I get a subtotal for each day of the month. How do I get a total just for the month? Thanks, Chris try this for the month (if dates in col a) and data to sum in col b =sumproduct((month(a2:a200)=3)*b2:b22) to add another variable =sumproduct((month(a2:a200)=3)*(b2:b22="joe")*c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "kanstrup" <kanstrup@discus...

delete permanently #2
Dear Sirs, I do not want to see some mail after deleting in Delete Items folder. Can I find any macro which can delete mail permanently? Best Regards Hi QT, please Press Shift + Del -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "QT" <web_tur@yahoo.com> schrieb im Newsbeitrag news:#0mbykcaEHA.3664@TK2MSFTNGP12.phx.gbl... > Dear Sirs, > > I do not want to see some mail after deleting in Delete Items folder. Can I > find any macro which can delete mail permanently?...

Update Quotes #2
Is there any way to permanently disable the "Update Quotes" feature? I do not have a stock portfolio and the Update Quotes feature takes upwards of 5 minutes to complete. I know that I can turn it off for each session, but I would rather just permanently disable it. Which version of Money? -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I d...

Copying Bitmap #2
I've a CWnd derived class e.g. CWMPPlayer4. How do I copy the current displayed data from the CMPPlayer4 object into a CBitmap object? Depends on how the data is created and whether or not all information is displayed on the screen. For example, see my essay on Screen Capture on my MVP Tips site. But this only works if the image contains everything you need to see, and is not obscured by other windows. If the actual image is is larger, then what you would have to do is create a memory DC, select a bitmap into it, and then call your OnDraw handler to write into the bitmap. The catch...

money install #2
Hi everyone, Just got my copy of Money Plus Home and Business (haven't installed yet). Anything I should know before installing? Someone said you should turn off UAC before installing and trun it back on afterwards (I have Vista Ulitmate) - that right? Anything else I should know? Thanks for your help. Jim If upgrading from a previous version the .mny file must live in a directory that you have full access to. Program Files is NOT a good place. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do n...

How to split a field into 2 fields?
I imported a spreadsheet from Excel and would like to split one field into two fields. Many of the records in Field1 have a note in parenthesis ( ). If a record has anything contained in parenthesis, I would like to move that data to Field2. What's the best way to accomplish this? To get the word inside the parenthesis try something like SecondWord: IIf(InStr([MyLeeter],"("),Mid([MyLeeter],InStr([MyLeeter],"(")+1,Len([MyLeeter])-InStr([MyLeeter],"(")-1),"") FirstWord: IIf(InStr([MyLeeter],"("),Left([MyLeeter],InStr([MyLeeter],&q...

XLUSRGAL #2
Hi, Will the file XLUSRGAL containing user defined charts always be located one level above the startup directory? ...

Column names are 1,2,3,....
I just noticed today that the column names in my excel spreadsheets are no longer alphabetical, but are numerical. The column marked "1" is defined by excel as column "1C", and row "1" is defined as row "1R". How can I get the column names to change back to the alphabet? Thanks Hi! G! Use: Tools > Options > General Remove check from "R1C1 reference style" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good...

Tasks and reminders #2
Many of my tasks repeat daily, and are not tied to any particular time. When adding a task, all I really want is the reminder. Here's what happens: Add task. Start Date: None. Due Date: None. Reminder box shows today's date at 0800, although it's now 1400. I change the reminder to 3/18 at 1200. Then I click Recurrence > Daily > Start 3/18. When I click OK, the reminder box has changed to 3/18 at 0800 and the Due Date box has changed to 3/18. I change the Due Date back to None and change the reminder back to 1200. Must be doing something wrong here? Is there a simp...

CFileDialog #2
Does anybody know the actual number of files CFileDialog can handle. I have a shared ntfs directory with between 30,000 and 80,000 files storing two file types. The filtering mechananism of CFileDialog box seems to break randomly when opening a view of the directory and appears to allow users to select files that CFile says don't exist. I'm also having problems simply opening up a view of the directory, I know that 30,000 files is a lot but at times we can't even get a view of the what files are actually in the directory. I have noticed the same problem when trying to ...

Parent Child report #2
Hi there does anyone have a parent child report? To list items by parent? Or by Matrix for that matter? Ivan The Reports Library has these two below: This report displays the Item Quantity List grouped by matrix code. Only matrix items are displayed, and each matrix code is displayed with its corresponding components. (April 26th, 2004) https://mbs.microsoft.com/downloads/customer/Custom_Matrix_Quantity_List.zip This report displays the Item Quantity List for Parent Child items. Only parent child items are displayed, and each parent code is displayed with its corresponding child ...

2 requests
Windows Vista Home Premium 32-bit Service Pack 2 Single user Two requests: One: Media Player Classic Some files will not play. Error indicates: FORMAT_WaveFormatEx {05589F81-C356-11CE-BF01-00AA0055595A} After searching the web I still cannot fix this. Is it codec missing? If so please someone point me to a site where I can download a fix. Two: Media Player Classic I would like to join 3 separate files into one continuous file. Suggestions for a program to do this. Of course - if anyone can recommend an alternative video player with most codecs included please let m...