SumProduct using Date Range

I need to sum a values using multiple criteria and a date range.

Example - I need to sum the total amount of all investors that have an "N" 
in the n/e column, are in a specified region, in a specified fund, and are 
between the dates of 2/1/2010 through 3/1/10.  Can't seem to get this to 
work?  Any help is greatly appreciated.

n/e	Inv Date	Investors	 Amount 	             Fund	Region
E	2/1/2010	Client 1	 $250,000 	                1	Asia
E	2/1/2010	Client 2	 $20,000 	                2	North America
N	2/1/2010	Client 3	 $14,000 	                4	North America
N	2/1/2010	Client 4	 $11,000           	10	Asia
E	2/1/2010	Client 5	 $7,000,000 	9	Europe
E	2/1/2010	Client 6	 $6,500	                6	Europe
E	2/1/2010	Client 7	 $5,000,000 	5	Asia
N	2/1/2010	Client 8	 $4,000 	                2	Canda
E	2/1/2010	Client 9	 $2,500,000 	2	Canda

0
Utf
5/19/2010 8:55:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
765 Views

Similar Articles

[PageSpeed] 55

Use cells to hold the criteria.

A1 = N
B1 = a specified region
C1 = a specified fund
D1 = 2/1/2010
E1 = 3/1/10

Then:

=SUMPRODUCT(--(n/e=A1),--(Region=B1),--(Fund=C1),--(Date>=d1),--(Date<=E1),range_to_sum)


-- 
Biff
Microsoft Excel MVP


"NMK" <NMK@discussions.microsoft.com> wrote in message 
news:FD8EE345-BD1D-4677-A828-2F2439461D10@microsoft.com...
>I need to sum a values using multiple criteria and a date range.
>
> Example - I need to sum the total amount of all investors that have an "N"
> in the n/e column, are in a specified region, in a specified fund, and are
> between the dates of 2/1/2010 through 3/1/10.  Can't seem to get this to
> work?  Any help is greatly appreciated.
>
> n/e Inv Date Investors Amount              Fund Region
> E 2/1/2010 Client 1 $250,000                 1 Asia
> E 2/1/2010 Client 2 $20,000                 2 North America
> N 2/1/2010 Client 3 $14,000                 4 North America
> N 2/1/2010 Client 4 $11,000           10 Asia
> E 2/1/2010 Client 5 $7,000,000 9 Europe
> E 2/1/2010 Client 6 $6,500                 6 Europe
> E 2/1/2010 Client 7 $5,000,000 5 Asia
> N 2/1/2010 Client 8 $4,000                 2 Canda
> E 2/1/2010 Client 9 $2,500,000 2 Canda
> 


0
T
5/19/2010 9:02:29 PM
Reply:

Similar Artilces:

Importing bank statement that has a different date format
Is there a way import a money format statement file that has a different date format? I use mm-dd-yyyy format in my Money but i would like to import statements that have the dd-mm-yyyy format! Curretnly i open the file in notepad and manually change the dates and then import the file into money. It would be nice to have an option to automate this during the import process! Thanks VJ ...

how to use Filter in Macro
Hi guys, I wonder why i cant use filter in macro? is there a guideline for us to use this function? -- Eladamri ------------------------------------------------------------------------ Eladamri's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35099 View this thread: http://www.excelforum.com/showthread.php?threadid=560155 Hi Columns("A:A").Select 'filter column A Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Eladamri" -- jetted ------------------------------------------------------------------------ jetted'...

Project Accounting Timesheet Integration using Int Manager
I am trying to import timesheets into Great Plains using Integration Manager. I only have the following data: Employee ID, Project ID, Cost Category ID, Date and Hours. I want to use the default values for the rest. I thought that this should be enough to get the transactions in but Integration manager is erroring out. It needs the total hours on the header. The system should be able to calculate the total hours on the header automatically. When I dont enter the total hours on the header when manually entering the timesheet, why is the Integration Manager expecting this value. Also, I ...

Using a cell�s information as a variable?
I have a Master worksheet with that contains cell information of a individual worksheet saved as a 4 digit employee number (2304.xls) fo example. ='X:\Time_Sheets\EMPLOYEE\[2304.xls]Sheet1'!$E$41 I would want to replace with.. ='X:\Time_Sheets\EMPLOYEE\[2261.xls]Sheet1'!$E$41 I have every row containing a different employee excel shee information and each row has 20 columns of specific employe information. I know I can highlight a row and use the find and replace functio (find 2304.xls and replace with 2261.xls) for example. Can I create a cell on the master Sheet and type i...

Doing a countif against a substring of the range to be scanned
I need to see how many cells in a particular range have a substring that matches the text against which I'm searching. So, for example, I've tried the following formula: COUNTIF(left(B2:B292,10),"="&left(cell("contents",P242),10)) I knew that this wouldn't work, but I was hoping that being able to evaluate the formula might lead me in the right direction. Excel won't accept the formula at all, though, so no chance to evaluate. I also tried this formula, which does let me evaluate: =COUNTIFS(B1:B292,"="&LEFT(P242,10)) ...

Can not minimize the outlook window while using MAPI to send mail
Dear All, I am using the MAPI to send a mail from outlook. The code works correctly. the only problem I am facing is that when it opens the new mail window in outlook, the new windows can be closed / resized but I can not minimize the window. So is there any restriction with MAPI? The code is given below: ------------------------------------------------- HMODULE hlibMAPI=LoadLibrary(_T("MAPI32.DLL")); if(hlibMAPI) { LPMAPILOGON m_fpMAPILogon = (LPMAPILOGON) GetProcAddress(hlibMAPI, "MAPILogon"); LPMAPILOGOFF m_fpMAPILogoff = (LPMAPILOGOFF) GetProcAddress(hlib...

creating opportunity on custom web page using CRM API
Hi All, I have an aspx form that uses the CRM API to create an opportunity into the crm application from a website. I set the usual text fields. The default compulsory fields are Topic and Poential Customer. When I enter an existing Account name ie, 'John Smith' in the Potential Customer field and submit the form, it comes up with an error: SOAP Server Application Faulted 80040216An unexpected error occurred.Object Type was not provided on attribute 'customerid'D:\crm\Build\3297\src\platform\include\OMCommon\CrmPropertyBagUtil.inl457 Source: System.Web.Services I beleive th...

Dynamic Charting By Dates
This is in reference to Jon Peltier's article on dyamic charts. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 My question is the example given is using data that goes down a column. Column A is the dates, Column B is the data, ect... My data flows across rows, so all my dates are in row 1 and the data is in row2. I would like some help converting Mr. Peltiers technique to fit my need. This code needs to go across a row instead of down a column. =OFFSET(AllDates,MATCH(StartDate,AllDates,1)-1,0,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1,1) As well as...

The updating is not applicable to the system in use
Hi to everybody! Everything starts with an error of Windows Update during the application of updatings and precisely: WindowsUpdate fails error: 800B0100. In the attempt of solution of the problem, someone has been recommended me of reinstallare Windows Update in autonomous formality unloading the packet related to the version of installed Windows. Being the version Windows Vista Home Premium 32 bits, the relative packet it results to be: Vista 32bit: Windows6.0-KB942288-v2-x86.msu 1.7 MB . Sinned that its execution is stopped by the signaling : "The updating is not app...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Using a VISTA printer.
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01CA6E8B.E3DB25A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable G'day to all. I have a slight issue. I have a Vista (Home Basic) machine hosting a = printer. I've shared this printer on the local LAN. I use my XP machine to connect to the printer (using the Vista User = account which has Administrative rights). When I initially did this thru the GUI, all is well (I made sure I = checked the appropriate boxes). When I shutdown and restart ei...

Formatting date fields
I could not seem to find the format syntax. I want to format a date field to display '------' when there is no date to print in a record. Can this be done? If no-one has a better suggestion then you can create a second field in your query with iif(IsNull(MyDate),Format(MyDate,"dd/mm/yy"),"----"). Show this field for display purposes and keep the real field for sorting and calculations. Evi "BobC" <Bob.NoSpammm@cox.net> wrote in message news:Kslzj.16736$097.15342@newsfe21.lga... > I could not seem to find the format syntax. > I want to format ...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Excel Dates #2
Whenever I initially enter a date, e.g., 6/1/2001, into a cell in an Excel spreadsheet, it is auto changed to make the year the current year, e.g., 6/1/2005. When retyped as 6/1/2001 the cell will then retain this date but I'm continually forced to double my efforts on date entries. This occurs on all worksheets including old, previously developed ones and newly opened ones. Any ideas why? Are you sure you are entering the year? XL assumes the current year if you only enter dd and mm, but I have not seen it change a specified year into the current one. -- Regards, Tushar Mehta ...

Using Pack and Go
I have never used this element of Publisher before so I need to know if there are things that one needs to be aware of before taking files to a commercial publisher? I have Publisher 2000 and the sheet that I have created for publicity has text boxes, photos and images. Will Pack and Go also pack the photos in the high resolution that I have them? Also, will the fonts be packed as well? I am using the Footlight MT Light, Abadi MT Condensed, and Verdana fonts. I don't want to get to the printer and find that these are going to be a problem. I am doing this sheet for a client an...

Sort records on Subform using hidden control
I have a form with a subform. The main form is for Jobs, and the subform lists the crew for each job. Each crew position has a number assigned to it, lowest being the highest "rank" in the crew. The name and postions of each crew member are selected by unbound combo boxes that list all personnel names and all possible positions. The position combo box has two columns, one with position title and the other with position number. The position number is column is hidden, since that field is only used on my side and not the user's. The bound PositionNumber box is hidden for...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

using colors
i get this message when i try to add a rule or when i try to delete a rule:"You cannot add antoher color because you have exceeded the maximum number of rules for this folder". i'd like to delete all of the color rules and start over. can i? Yes. View | Current View | Customize Current View | Automatic Formatting. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "lindap" <anonymous@discussions.microsoft.com> wrote in mes...

Does anyone use "location" field in Outlook?
After all these years, I just noticed when I go to make an appointment in Outlook (2003version, with BCM), that there's a second field after "subject" which would seem to be the one everbody mostly uses. The appointment fields are: 1. SUBJECT: (example: "Conference call with Joe and Mary) 2. LOCATION: (does anyone use this?) Is the idea to put something like "Annapolis, MD" meaning maybe a meeting there or something? I know it's a hokey question, but wondered about some examples of how other people are using it. Thanks, G Standard following us...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

Reporting services available for use with access?
I am building a DB in access for my project managers, some will be using just access. Is it possible that report builder can be used in Access? In some way? ...

Use of Indirect
I am trying to do something similar to what I have seen on one of these discussions. I have sheetnames of the other worksheets in a workbook in row 1 of a worksheet and am wanting to refer to a formula in cell A33 of each of the works - I've always had problems getting my head around this "INDIRECT" function. For instance in D31 I'm trying to get the result ="SheetnameD1"!A33 using something like: =INDIRECT("'"&D1&"'!A33",), but there are two problems with this, 1 this returns a #REF! error, and 2 copying it across the rows wil...

Importing data to Excel using MS Query
I am importing data into Excel 97 using the data query functions and MS Query. I can set simple criteria, but I am also sure I could do more with this if only I knew how to. Could anyone recommend some (simple!) reference material that explains more about setting up SQL queries. TIA, John. -- John Reynolds This is a pretty good site for a basic MS Query tutorial. http://www.rdg.ac.uk/ITS/info/training/notes/excel/query/ For learning SQL...I always recommend: The Practical SQL Handbook: Using Structured Query Language It's not Microsoft specific, but it will teach you what you ...

Using Lotus 123 command in excel 2003
I m using Office 2003. But problem is that there i could not able to use lotus 123 command. Why so? Please help me Rao Ratan Singh Why would you? I believe it allows for some things to be done and you can go to tools>options>transition to change a few things -- Regards, Peo Sjoblom (No private emails please) "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:0267594A-0E71-4A73-89B1-C78B788545C7@microsoft.com... >I m using Office 2003. But problem is that there i could not able to use > lotus 123 command. > > Why so? ...