#### SUMPRODUCT with date range question

```Hi all,

I'm having trouble creating a formula as follows:
Column A: either Y or N
Coumn B: a forecasted date
Column C: \$\$ amount of transaction

What I would like to do is create a report that shows the sum of column C
for anything where column A is "Y" and the date in column B is today's date
or up to 90 days after today's date (Today +90)...  I'm having problems with
the date criteria, and would appreciate your help! Thanks
```
 0
RobV (2)
1/31/2005 3:15:06 PM
excel.misc 78881 articles. 5 followers.

1 Replies
486 Views

Similar Articles

[PageSpeed] 47

```try something like this

=SUMPRODUCT((A7:A10="Y")*(B7:B10>=TODAY())*(B7:B10<=TODAY()+90)*C7:C10)-

Don Guillett
SalesAid Software
donaldb@281.com
"Rob V" <RobV@discussions.microsoft.com> wrote in message
news:92CAC6F0-DD20-457B-A28D-4B06B1106F9C@microsoft.com...
> Hi all,
>
> I'm having trouble creating a formula as follows:
> Column A: either Y or N
> Coumn B: a forecasted date
> Column C: \$\$ amount of transaction
>
> What I would like to do is create a report that shows the sum of column C
> for anything where column A is "Y" and the date in column B is today's
date
> or up to 90 days after today's date (Today +90)...  I'm having problems
with
> the date criteria, and would appreciate your help! Thanks

```
 0
Don
1/31/2005 3:55:24 PM
 Reply:

Similar Artilces:

date stamp #2
I am a new user to excel and would like to know how I put a stamp in the corner of my workbook that says the file that it is under. Ex h:\xw\... Also would this change if I change the file it is in. Thankyou Jessica ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =LEFT(CELL("filename",A1), FIND("]", CELL("filename",A1))) Note that the file must be saved for this to work. In article <jdorion.vgeay@excelforum-nospam.co...

Date #6
hi I have a workbook that contains several formatted cells cell "A1" is formated as dd/mm/yyyy and has the today() formula in it what i want to do is to copy the value of "A1" and paste it into "A4" ie. "A1" = 13/01/2005 i only want the text value to be copied and pasted into A4 "A4" = 13/01/2005 When i try this i get the numeric value of 38365 which is excels text value of the date. I need the above format thanks in advance kevin Pre format or use edit>paste special or use a formula =text(a1,"yourformathere") or...

Inline Jpgs and more questions
A few questions: 1. How do you view jpg attachments inline? Similar to Outlook Express. 2. After upgrading to Outlook 2002 from 2000, you cannot even select all, then hit enter to view all in an external viewer. Why does it seem we went backwards? 3. Even the Progress Bar for sending and receiving email is more accurate in Outlook Express. In outlook, it appears it just takes the number of enclosures and divides by that number (immaterial of their size), where OE truly shows progress on the bytes. Very helpfull when using dialup. Why does it seem the free email package is bette...

scanpst.exe questions
Hi, After much hunting, I found scanpst.exe, which I'm told will help me overcome mailbox correuption problem. When I run it, I'm told that .pst that I'm trying to fix does not exisit specifically - despite the fact that when I look at it's details, this 'non-existant" file has 34, 444 bytes in it. Has anyone seen this type of helpful behavior beore and do you have any suggestions about how to out wit it? It all depends on how corrupt the mailbox is, but I've seen situations where putting a null character in the first byte of the file allowed scanpst t...

Brainstorming question
I have an XP box that is set up in an office as an incoming fax, it does not have Norton installed and it is not a server, XP Pro only. The incoming fax folder is shared. We also have a server 08 and each user has a login script that launches the shared fax folder. The users complain that occasionally the shared Fax folder stops responding when they access it from their desk - this is corrected by disconnecting and rebooting their machine. Any ideas on how to fix this non responsive issue or can I run a task that will copy and move the faxes as they come in to the serve...

Add a date to a Userform
I am using Office 2010 in advance of roll-out in our organization. I have been testing some of our VBA enabled templates for compatibility and have run into a problem. Specifically a lose of the Calendar control. Since we have a number of Userforms that use the calendar control, I need to come up with some sort of replacement. Any suggestions? I thought there was a date picker control, but I can not find it (I have installed the complete Office 2010 Professional Plus) The calendar control uses the library c:\program Files\Microsoft Office\Office13\MSAL.OCX Usually th...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Insert date into file import
I have a database that imports a .csv file generated by anotherprogram. Unfortunately this program doesn't include a date in eachrecord in the .csv file.I can import the .csv file into my table alright, but I need to attach/insert a date (which I can get via VB code) into each importedrecord. Any ideas?At the moment I'm using the Docmd.TransferText command. have you tried importing the data, then running an Update query on thetable, to add a date to each record?hth<b_lwalker@hotmail.com> wrote in messagenews:1174264665.772034.212550@n59g2000hsh.googlegroups.com...> I have a ...

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

date format #32
i have a sheet where in some cells the date are in ddmmyy format along with text & in some cells mmddyy format along with text. is there any command to change all in mmddyy format On Sat, 20 Aug 2005 03:40:03 -0700, "Ankur" <Ankur@discussions.microsoft.com> wrote: >i have a sheet where in some cells the date are in ddmmyy format along with >text & in some cells mmddyy format along with text. > >is there any command to change all in mmddyy format You need to describe more precisely exactly what is in the cells, and how one can tell the difference betwee...

Excel default date format
1) The default date format for Excel is "21-Mar" when I enter date recognized data. This is unusable for me, I need the year displayed. How do I change the default date format to display the year. 2) With default also, Excel sometimes requires "=" in equations and sometimes not; i.e., if I enter a '-' it converts the cell to an equation. How do i stop this so that it only makes equations when I enter '='. 3) How do I get help on Excel inconsistancies like the above in Excel documentation anywhere? mjk #1. If I type in the date that includes the ye...

Question about right click in a CTreeControl!
Hello! Having problems about right clicking on an item in a tree control. The item that is clicked on is not the right one. If I have three items in a tree. Here is my problem. I click on the first item with normal click. I click on the third item with the right click mouse. In this event I had a messagebox telling me which item is selected, and this one comes out with the first item no mather what. I also tried to get into the OnSelchangedItemfieldtree function - but with no luck. How can I right click on the third one and get its name out of it? Does anyone know? -- - Lars I guess u...

Display ranges from one worksheet to another
I am running excel 2003. I have been given the task of creating a workbook where on one main sheet I want to display named ranges from up to 4 other worksheets based on the user selection. The ranges are work weeks and we want to be able to display 8 weeks at a time for resource planning. Can someone point me in a direction where I can find a way to do this? Thank you. Check Ron's site under the Copy/Paste section. http://www.rondebruin.nl/tips.htm "Opal" <tmwelton@hotmail.com> wrote in message news:eb392038-e6ab-46b0-a3d6-eb0df5c7a0fd@h2g2000vbd...

how do i set an auto date function in excel or word?
I would like all documnts in both word and excel to automatically date as the document or report is prepared. =NOW() "hpkcommish" wrote: > I would like all documnts in both word and excel to automatically date as the > document or report is prepared. for excell Go to View> Header & footer >custom header footer then click on the clock pic & the date pic (choose 1st if you want it in the left middle or right feild) For word basically the same Cheers peterm "hpkcommish" wrote: > I would like all documnts in both word and excel to automatica...

Outlook question to challange all you MVP's out there.
I am slowly migrating my users from a single Exchange 2000 box, to a cluster Exchange 2003 server. I have 155 gas stations all over the east coast that have pc's in them with email. My problem is that all the stores are not on my corporate domain. What happens when I launch outlook is it prompts me for the username, password, and domain. Once I enter it in, it logs in fine, and there are no issues. The odd part about this is that on my "old" Exchange 2000 box, it doesn't prompt the user. It just logs them in since their username and password in the store are the same as...

May I post here questions about SQL queries against Excel via ODBC?
and if not, can you recommend of a relevant forum? For example, I'm desperately looking for an answer to the question: How can I retrieve the row number of a record in a query? Somethin like: select x.*,rownum() from sheet1\$ as x Thanks in advance, Noam ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try posting in the Security Forum, under SQL Server Security, that's where most people go when they have questions about SQL. ...

Problem with copying a to from date to another cell
I am trying to enter the first 7 days of the year in one cell. ie: A1 -> 1/2/05 to 1/8/05 And then I want to drag that down to 53 cells and have it copy in the following format. ie: A2 -> 1/9/05 to 1/15/05 A3 -> 1/16/05 to 1/22/05 etc... Is this possible? I would do it by using two separate columns, typing in the two top row dates, the highlight the area you wish to fill, then Edit > Fill > Series > Trend..........then when you have the two columns of dates, use a third helper column with this formula copied down...... =TEXT(A1,"mm/dd/yy")&...

Word 2007 Show Markup checkmarks and Date/Time Issue
Hi, I have a new HP desktop computer and finally got Word 2007. I have two questions. In the Review section of Word 2007, the Show Markup subsection has checkmarks for ink, comments, etc. as the default selection. Is there any way to change this default and uncheckmark them so it isn't automatic? I have looked everywhere online for a solution and can't find one. Second, in the bottom right-hand corner of my monitor, the time is showing as usual. However, when I first got my computer (last week), it also showed the date (I loved this feature). After re-starting my compute...

Unusual formatting question
Hi bit of an odd question here.. I have a list of URL's that are currently in an Excel file. The URL' are listed in one column with one on each row, so for example: link1.ac.uk link2.ac.uk link3.ac.uk link4.ac.uk link5.ac.uk What I need to do is merge all of these links into one long line an add a plus sign between them. In the above example this would mean: link1.ac.uk+link2.ac.uk+link3.ac.uk+link4.ac.uk+link5.ac.uk Is there a way of getting Excel (or any other programme) to take th original list and reformat it as above? I have several clusters of links that need to be refo...

Fun with SUMPRODUCT
Trying to solve for the following: Count if 1) Priority = "Must" AND 2) Status <> "Closed", "Accepted", or "Testing" AND 3) Functional Area begins with "RPT" AND 4) Estimated Effort = "XL" Wondering how best to modify the formula below to represent parts 2 and 3 above. How to represent contains "rpt" or begins with "rpt" (if wildcards were permitted this would be too easy!) and accommodate the variable status values we wish not to include. SUMPRODUCT(--(C2:C345=&q...

Date Format Problems
Is there anyway to make excel change the numbers I type in to the appropriate date? If I type 112603, how can I get the cell to display 11/26/03. I have tried formatting the cell, but it just doesn't seem to come out right. Any help would be greatly appreciated. Thank you Jennifer Only through VBA. See Chip Pearson's website. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben XL2002 On Fri, 5 Dec 2003 08:06:01 -0800, "Jennifer W" <icecream3@earthlink.net> wrote: >Is there anyway to make excel change the numbers I type in to the appropriate date?...

database question #2
Creating a database in excel for mailing labels. Fieldnames: Title (Mr. & Mrs.), Fname (John), Lname (Jones), Add, City, etc. Is there a way to handle married people who want to be addressed as: Ms. Sally Smith and Mr. John Doe Is there an alternative field to be set up? Can't figure it out unless it's just a code field and then a manual change. Thanks if there is an easy solution. A field is just a space to contain data, so you can put what you like i it -- Message posted from http://www.ExcelForum.com ...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Calendar To Stay at Last Date Entered
I have a calendar in a form. We do data entry for information that could have occurred today, yesterday, or a couple of days ago, like over a weekend. The form always goes back to todays date. How do I get the calendar to stay at the last date entered? Somehow you will need to record the last date entered in a table somewhere. I use a system table for such items. Bill wrote: >I have a calendar in a form. We do data entry for information that could >have occurred today, yesterday, or a couple of days ago, like over a weekend. > The form always goes back to todays date. How ...

Due date VS processing date in Money '06
This is really bothering me. In my previous version, when I entered a due date for an epay, my register showed that date. It didn't remind me that I need to have XXX dollars in my account on due date - 4 and then change the date in my register to due date - 4. I want my register to show the due date I entered. I have enough sense to know I need money in my account. How can I change this? The check box about using processing date instead of entered date is NOT checked. Did my upgrade magically switch me to a bill payment service that is actually drafting my account early and pu...