passing dates in a conditional sum(if)

I have a workbook with 5 sheets.
Sheet1 is Customers
Sheet2 is YTD Sales 07
Sheet3 is Total Sales 06
Sheet4 is Total Sales 05
Sheet5 is Total Sales 04

Customer sheet has this basic formula in the columns for each year's sales 
based on the customer's number and the variable of the month that the sales 
was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))}

The problem is that the accounting program exports the invoices with the 
MMDDYY for each invoice. The $j$1 is the number of the month I limit it up 
to. I have a column in each sales sheet with the month() function to pull 
the month out. This works ok but it always pulls the entire months from the 
completed years and present year's sales always look behind. Completed 
months give good info but to run the report in mid month is deceiving.
I want to be able to put a variable date range in the sum(if) array and have 
run into a brickwall. I have tried using the Date(,month(),day())with no 
luck. I think I have to use it because the years are different.
I think that the formula should read something like "if the customer number 
is the same and the invoice date is <= $j$1  then sum invoice amount". J1 is 
the date as 3/15/07 and date07 is the range where it takes the invoice date 
and strips it of the year using date(,month(e2),day(e2)) so that the date 
reads 3/15 not 3/15/07 or 3/15/06...
{=SUM(IF((Custnumber07=A5)*(date07<=DATE(,MONTH($J$1),DAY($J$1))),amount07))} 
and get #N/A
Any help or guidance is greatly appreciated. I have searched on Chip 
Pearson's, Microsoft's and others websites with no luck.
Thanks,
Lee Coleman 


0
Neophyte
3/30/2007 4:51:19 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
333 Views

Similar Articles

[PageSpeed] 2

Maybe this

=SUMPRODUCT(--(Custnumber07=A3),--(date07<=DATE(YEAR(date07),MONTH($J$1),DAY($J$1))),amount07)

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message 
news:eWC0zbocHHA.4344@TK2MSFTNGP02.phx.gbl...
>I have a workbook with 5 sheets.
> Sheet1 is Customers
> Sheet2 is YTD Sales 07
> Sheet3 is Total Sales 06
> Sheet4 is Total Sales 05
> Sheet5 is Total Sales 04
>
> Customer sheet has this basic formula in the columns for each year's sales 
> based on the customer's number and the variable of the month that the 
> sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))}
>
> The problem is that the accounting program exports the invoices with the 
> MMDDYY for each invoice. The $j$1 is the number of the month I limit it up 
> to. I have a column in each sales sheet with the month() function to pull 
> the month out. This works ok but it always pulls the entire months from 
> the completed years and present year's sales always look behind. Completed 
> months give good info but to run the report in mid month is deceiving.
> I want to be able to put a variable date range in the sum(if) array and 
> have run into a brickwall. I have tried using the Date(,month(),day())with 
> no luck. I think I have to use it because the years are different.
> I think that the formula should read something like "if the customer 
> number is the same and the invoice date is <= $j$1  then sum invoice 
> amount". J1 is the date as 3/15/07 and date07 is the range where it takes 
> the invoice date and strips it of the year using date(,month(e2),day(e2)) 
> so that the date reads 3/15 not 3/15/07 or 3/15/06...
> {=SUM(IF((Custnumber07=A5)*(date07<=DATE(,MONTH($J$1),DAY($J$1))),amount07))} 
> and get #N/A
> Any help or guidance is greatly appreciated. I have searched on Chip 
> Pearson's, Microsoft's and others websites with no luck.
> Thanks,
> Lee Coleman
> 


0
bob.NGs1 (1661)
3/30/2007 7:49:56 AM
Thanks for the response. The ranges of Customer07 are not the same as Date07 
and Amount07(they are =). If I make all the named ranges the same number of 
rows(10205 is the largest worksheet) and some are blank, will this mess up 
sumproduct? Or do they have to be the same range?
I think I read that they had to be the same for sumproduct and that is why I 
chose sum(if) as I never could get sumproduct to work.
What does the -- mean?
When I enter sumproduct in the cell I get a #value! error.

Thanks,
Lee Coleman

"Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
news:OTdl%23$pcHHA.4864@TK2MSFTNGP05.phx.gbl...
> Maybe this
>
> =SUMPRODUCT(--(Custnumber07=A3),--(date07<=DATE(YEAR(date07),MONTH($J$1),DAY($J$1))),amount07)
>
> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
> "Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message 
> news:eWC0zbocHHA.4344@TK2MSFTNGP02.phx.gbl...
>>I have a workbook with 5 sheets.
>> Sheet1 is Customers
>> Sheet2 is YTD Sales 07
>> Sheet3 is Total Sales 06
>> Sheet4 is Total Sales 05
>> Sheet5 is Total Sales 04
>>
>> Customer sheet has this basic formula in the columns for each year's 
>> sales based on the customer's number and the variable of the month that 
>> the sales was done. 
>> {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))}
>>
>> The problem is that the accounting program exports the invoices with the 
>> MMDDYY for each invoice. The $j$1 is the number of the month I limit it 
>> up to. I have a column in each sales sheet with the month() function to 
>> pull the month out. This works ok but it always pulls the entire months 
>> from the completed years and present year's sales always look behind. 
>> Completed months give good info but to run the report in mid month is 
>> deceiving.
>> I want to be able to put a variable date range in the sum(if) array and 
>> have run into a brickwall. I have tried using the 
>> Date(,month(),day())with no luck. I think I have to use it because the 
>> years are different.
>> I think that the formula should read something like "if the customer 
>> number is the same and the invoice date is <= $j$1  then sum invoice 
>> amount". J1 is the date as 3/15/07 and date07 is the range where it takes 
>> the invoice date and strips it of the year using date(,month(e2),day(e2)) 
>> so that the date reads 3/15 not 3/15/07 or 3/15/06...
>> {=SUM(IF((Custnumber07=A5)*(date07<=DATE(,MONTH($J$1),DAY($J$1))),amount07))} 
>> and get #N/A
>> Any help or guidance is greatly appreciated. I have searched on Chip 
>> Pearson's, Microsoft's and others websites with no luck.
>> Thanks,
>> Lee Coleman
>>
>
> 


0
Neophyte
3/31/2007 4:35:45 AM
Reply:

Similar Artilces:

Custoemr Promise Date
From the Sales Item Detail Entry window, Ctrl+1 will open the Manufacturing Series Sales Item Detail window. Would anyone know the SQL table that stores the values in this 2nd table? I am most interested in the Customer Promise Date. Thank you, M. SOP10200 -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Maria" wrote: > From the Sales Item Detail Entry window, > Ctrl+1 will open the Manufacturing Series Sales Item Detail window. > ...

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Date query "Excel VBA"
Is there anyway i can use vba code to create a user input box on a sale worksheet that will filter the sales orders and only display the order that equal the date entered into the input box by the end user. I am already using the autofilter feature but would like to take it on step further and make it easier for the admin clerk. Thank-Yo -- Message posted from http://www.ExcelForum.com You didn't like yesterday's suggestion???? http://groups.google.com/groups?threadm=4110273A.B3C20CDD%40msn.com "avilla <" wrote: > > Is there anyway i can use vba code to cr...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

Conditional Format
Hi, I want to assign a number to a certain conditional format. When the condition is true it should put a 1 in the cell and when the condition is false a zero or nothing should be assigned. Anyone? I seem to make the wrong code all the time. Thanks, Johanna Use the same test that the CF uses, and return 1 for TRUE and "" for FALSE. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jootje" <Jootje@discussions.microsoft.com> wrote in message news:CB0B3201-2DD9-466A-94FF-108691C77FAF@microsoft.com... > Hi, > >...

Date format converted from UK to US format
I have a workbook with multiple tabs, each of which take data from mutlipe spreadsheets using a Query. I then use the code below, to consolidate all the data on the tabs into one table, so i can PivotReport on it. The problem comes from the columns with dates in it - they import using the Query in UK format, but after the code has brought the tabs into one, they become US format as text. Can anyone help correct this, as i'm having to use a workaround formula to rearrange the cells at the moment. Sub collate2() Dim firstRow As Double, lastRow As Double, srcRng As Range,...

Formatting Dates
I want to enter a date in text as in: January 25, 2001 And then i want Excel to display it as: 1/25/01. When i tryed to format the cells the date is displayed as I have typed it in. Can anyone help me do this? Select all the cells you want in this format, right click, Format Cells, on the Number tab, select 'Date,' then '3/14/98' as the type. Then click OK. When I do this, and enter 'January 25, 2004, the cell changes it to 1/25/04. "davidian" wrote: > I want to enter a date in text as in: > January 25, 2001 > > And then i want Excel to displ...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

copy date from one column to another
I want to copy data from one column to another column, but add the copied value to existing data. The field I'm copying from contains dates. The field I'm copying to contains text. For example: Before the copy: Column A Column B 3/21/2008 Lorem Ipsum dolor...etc. After copy: Column B Lorem Ipsum dolor...etc. Date in: 3/21/2008 How can I accomplish this? Thanks for any help. Regards. Sub addtocolb() For Each c In Range("a2:a4") c.Offset(, 1) = c.Offset(, 1) & _ " Date in: " & Format(c, "mm/dd/yyyy&...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

Dates #18
I am trying to insert dates into Excel. For the most part the dates show up, but I have two cells that after I enter the date they say "38534". What is the deal with that? I do not believe that this is caused by the formula in the following cells because the rest of the cells are fine. Does this make any sense? -- --"The second hand unwinds..."-- Vaya con Dios, my darling; Vaya con Dios my love. XL stores dates as integer offsets from a base date. 38534 days after 12/31/1899 (more or less) is 7/1/2005, which is the displayed date. Format your ce...

Today's Date
I need to convert the date to MM/DD/YYYY format from MM/DD/YY and then delete all rows in the current worksheet that don't have today's date in column C. The number of rows is variable each day. This is the code I was using, however it does not work now and I'm not sure why, any help you can provide is appreciated. Sub OnlyTodaysDate() ' Change date to read format mm/dd/yyyy Dim RowNum As Integer Dim NextValue As String Dim NextDate As Date For RowNum = 1 To Range("C1").CurrentRegion.Rows.Count NextValue = ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Dates #6
Hello I need to compare a number in one cell to a date in another to see if they match (I am matching the day in the date). A B 15 02/15/09 =true 23 01/23/09 =true 1 05/11/08 =false 3 02/30/09 =false Can this be done? Thanks....... 30 01/30/09 15 02/15/09 1 02/01/09 If I understand what you want: =A1=DAY(B1) Copy down as needed. -- Biff Microsoft Excel MVP "teresa" <teresa@discussions.microsoft.com> wrote in message news:632BC39D-B355-496F-BF85-CBF17CD0E806@microsoft.com... > Hello > > I need to compare a number in one cell to a date in...

Populate date into excel from DataSet/DataTable
Hi there, We are developing a WPF windows application in .NET 3.5 & VS 2008 We have the following requirement 1. An excel template will be available in server side, in which the webservice is being hosted. 2. We need to call a webservice to get the data from database and populate the data in the predefined excel template available in the step1. 3. Then download the data populated excel to the client side. Is there any elegant way of doing this in server side, since its webservice multiple client might call this consume this to get this done. Please advice. Thanks, Dhans...

Passing data between forms
Hi I am working on a project someone else started, and he has this code: If STR_FormName = "FMS_ToDo" Then Page47.SetFocus He obviously expects the name of the last form to be here, but it is not. In fact, when I put Option Explicit at the top - this field was undefined. How is this supposed to work? Stapes ...

Pivot Table, Date Sorting?
I have a pivot table with the data grouped by year,month, date. Year and Month sort ok, but date seems to be sorted numerically based on the day. I get the following: 10 - 30 followed by the dates 1 - 9. I've changed the date format to several different formats with no luck. I've double-checked to sort order and everything looks ok. Any Ideas? Sounds like they are not real dates, but are in fact either a numerical representation of a date or text. They need to be real dates in a format that Excxel recognises to sort properly, otherwise you will get exactly the problem you have g...

Finding Previous Date in Date Range With Duplicate Dates
I have a small worksheet that tracks sales by date and price. I have an array formula that looks through the date ranges (Start Date and End Date) to find the range that occurs in this period (current month) and then looks to the previous period to get the variance. Here's a sample formula: {=SUM(IF(($G$2>=BOP!$B$29:$B$71)*($G$3<=BOP!$C$29:$C$71),OFFSET(BOP!$J$29:$J $71,-1,0),""))} The problem I am running into is that is where 2 consecutive periods are the same (where we had a pricing change in the middle of the date range, so we showed 2 consecutive periods with diffe...

IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!!
IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!! Hi guys, If you are serious about your professional career and pass IT Certification exam in first attempt then Visit http://www.FreeExamKing.com and get latest Questions And Answers Dumps for Microsoft, Oracle, Cisco, CompTIA and many more, i have used and found it very helpful. ...

Parameter based on Date Field
I have a customer table with a Date of Birth field. I want to be able to prompt a user to enter a begin and end date to print out birthday letters based solely on the user entering the begin birth month and day and the end birth month and day, and not the year. How can this be done? On May 8, 1:26 pm, dh1069 <dh1...@discussions.microsoft.com> wrote: > I have a customer table with a Date of Birth field. I want to be able to > prompt a user to enter a begin and end date to print out birthday letters > based solely on the user entering the begin birth month and day and the end ...

Timeline with critical date markers
I want to create a chart which shows a series of data inflating over time, with vertical bars marking critical points in time. These critical dates are input in individual cells in the spreadsheet, rather than being series. I don’t want to just ‘draw’ lines and overlay them on the chart because the critical points will change when the dates change. Any ideas? Hi, Here is a collection of examples for adding lines to charts. http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#Ad...

Date formatting Help
Macro - Help How can you select a cell with a Sunday's Date to give you th following Sunday's Date when execute -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27551 To get next Sunday's date, just add 7 to this Sunday. So something like: cell.value + 7 -- Regards, Fred Please reply to newsgroup, not e-mail "Db1712" <Db1712.1f88gn@excelforum-nospam.com> wrote in message ...

Show Last Date in Time Series Chart
I have a time series line chart with dates on the x-axis. The dates run in ascending order , left to right. The last date in the series does not appear on the chart. After numerous attempts with different scenarios using the various functions under the "Scale" tab in the "Formatting Axis" dialoge box, I have been unable to display the last value in this series on the chart. Is there a method for doing this? Thank you in advance for taking time to responed. Is the last value missing, or is the last date not labeled on the axis? All the values should appear automat...

Passing arguments to procedures
There is something fundamental that I have failed to grasp about passing arguments to procedures. I have a form featuring a set of unbound controls displaying dates and the corresponding days of the week. (For some reason, the long-date format, on my computer, does not include the latter, as the documentation suggests it might, so it has to be separately calculated and displayed.) A default date is displayed in each control, on Form_Open. This can then be manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with each day/date control pair. In the case of the control ‘St...

Automaticly ad date by select
Can i automaticly fill in the date by selecting a cel? And when possible automaticly a button or something to change the date + and - at least for the day count and if possible also to change the month. For example a button left and a button right from the cel with the date to change the value. -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ Hoi Geert See this page http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Geert" <veilingsitesssNO@spamhotmail.com> wrote in message news:op.tilzuqrrm1sb3e@...