Sumproduct entire month from mmddyyy?

In A1 I have the date: mmddyyy.  I need a sumproduct formula to return
the sales for a sales person for the entire month and year for the
date in A1 even though just one day is entered into A1. Unfortunately
on this report, I can only reference the mmddyyy date in A1.

A2:A1000 has dates mmddyyy for each sale made
B2:B1000 has salesperson that made the sale
C2:C1000 has sales amount of each sale
0
wx4usa
1/8/2010 6:18:14 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
554 Views

Similar Articles

[PageSpeed] 37

Did you check your earlier post for the answer

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"wx4usa" <wx4usa@gmail.com> wrote in message 
news:cee21257-fe6a-4025-916a-897117e72c9a@a32g2000yqm.googlegroups.com...
> In A1 I have the date: mmddyyy.  I need a sumproduct formula to return
> the sales for a sales person for the entire month and year for the
> date in A1 even though just one day is entered into A1. Unfortunately
> on this report, I can only reference the mmddyyy date in A1.
>
> A2:A1000 has dates mmddyyy for each sale made
> B2:B1000 has salesperson that made the sale
> C2:C1000 has sales amount of each sale 

0
Don
1/8/2010 6:20:47 PM
Try this...

=SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000)

-- 
Biff
Microsoft Excel MVP


"wx4usa" <wx4usa@gmail.com> wrote in message 
news:cee21257-fe6a-4025-916a-897117e72c9a@a32g2000yqm.googlegroups.com...
> In A1 I have the date: mmddyyy.  I need a sumproduct formula to return
> the sales for a sales person for the entire month and year for the
> date in A1 even though just one day is entered into A1. Unfortunately
> on this report, I can only reference the mmddyyy date in A1.
>
> A2:A1000 has dates mmddyyy for each sale made
> B2:B1000 has salesperson that made the sale
> C2:C1000 has sales amount of each sale 


0
T
1/8/2010 6:24:21 PM
On Jan 8, 1:24=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this...
>
> =3DSUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=3Dtext(A1,"myyyy")),--(B2:B1000=
=3D"salesperson"),C2:C1000)
>
> --
> Biff
> Microsoft Excel MVP
>
> "wx4usa" <wx4...@gmail.com> wrote in message
>
> news:cee21257-fe6a-4025-916a-897117e72c9a@a32g2000yqm.googlegroups.com...
>
> > In A1 I have the date: mmddyyy. =A0I need a sumproduct formula to retur=
n
> > the sales for a sales person for the entire month and year for the
> > date in A1 even though just one day is entered into A1. Unfortunately
> > on this report, I can only reference the mmddyyy date in A1.
>
> > A2:A1000 has dates mmddyyy for each sale made
> > B2:B1000 has salesperson that made the sale
> > C2:C1000 has sales amount of each sale

I did check earlier post, but I could not figure out how to add the
year and the month from mmddyyy.

Biff's answer did it! Thanks Biff!
0
wx4usa
1/8/2010 6:40:15 PM
On Jan 8, 1:24=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this...
>
> =3DSUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=3Dtext(A1,"myyyy")),--(B2:B1000=
=3D"salesperson"),C2:C1000)
>
> --
> Biff
> Microsoft Excel MVP
>
> "wx4usa" <wx4...@gmail.com> wrote in message
>
> news:cee21257-fe6a-4025-916a-897117e72c9a@a32g2000yqm.googlegroups.com...
>
> > In A1 I have the date: mmddyyy. =A0I need a sumproduct formula to retur=
n
> > the sales for a sales person for the entire month and year for the
> > date in A1 even though just one day is entered into A1. Unfortunately
> > on this report, I can only reference the mmddyyy date in A1.
>
> > A2:A1000 has dates mmddyyy for each sale made
> > B2:B1000 has salesperson that made the sale
> > C2:C1000 has sales amount of each sale

Hi Biff, Me again

=3DSUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=3Dtext(A1,"myyyy")),--
(B2:B1000=3D"salesperson"),C2:C1000)

How do I make the above formula work for a date range?  11/01/2009 to
01/31/2010
0
wx4usa
1/8/2010 8:01:08 PM
Use cells to hold the date boundaries:

A1 = start date = 11/01/2009
B1 = end date = 01/31/2010

=SUMPRODUCT(--(A2:A1000>=A1),--(A2:A1000<=B1),--(B2:B1000="salesperson"),C2:C1000)

-- 
Biff
Microsoft Excel MVP


"wx4usa" <wx4usa@gmail.com> wrote in message 
news:d5b28c80-65a0-47ef-9de3-ecc12fe0f6c3@34g2000yqp.googlegroups.com...
On Jan 8, 1:24 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this...
>
> =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000)
>
> --
> Biff
> Microsoft Excel MVP
>
> "wx4usa" <wx4...@gmail.com> wrote in message
>
> news:cee21257-fe6a-4025-916a-897117e72c9a@a32g2000yqm.googlegroups.com...
>
> > In A1 I have the date: mmddyyy. I need a sumproduct formula to return
> > the sales for a sales person for the entire month and year for the
> > date in A1 even though just one day is entered into A1. Unfortunately
> > on this report, I can only reference the mmddyyy date in A1.
>
> > A2:A1000 has dates mmddyyy for each sale made
> > B2:B1000 has salesperson that made the sale
> > C2:C1000 has sales amount of each sale

Hi Biff, Me again

=SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--
(B2:B1000="salesperson"),C2:C1000)

How do I make the above formula work for a date range?  11/01/2009 to
01/31/2010 


0
T
1/9/2010 12:03:05 AM
Reply:

Similar Artilces:

How do I Remove and replace entire Publisher website?
-- Ole Crep Delete all of the files. Upload new ones. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "OleCrepitation" <olecrep@hotmail.com(donotspam)> wrote in message news:5B675A63-E2B2-4461-9DA4-CF52FCADB16F@microsoft.com... > > -- > Ole Crep I use a File Transfer Program to gain access to my website files on the server. From the ftp, gather all the files of the website and click on delete. Then, gather all the new files on your computer and move them to the website and unclick. -- Do...

how do I reverse an entire column of names that have the last nam.
how do I reverse an entire column of names that have the last name first, first name and then middle name or initial .....in an excel workbook? Thanks! Check out, <Data> <Text To Columns> To separate the names into separate columns, and then, if you wish, you can concatenate them back into a single column. Post back with further questions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------...

standard filter "older than x months" limitations
Hi, anyone who knows how to work around the standard filter limitation of "older than x months"? You are only able to enter a number between 0 and 100! I need to have a saved query (=dynamic list) of contacts who are older than e.g. 30 years. I assumed that I could find this easily throught the standard methods, namely create a view for contacts with birthdate "older than x month" 1230 (=12*30) but apparently there's a restriction in this?! Anyone knows how to work around this with equal effort? I wonder why those limitations are built in?! Microsoft could at...

Conditional formating for the month
I have a column with the birthday of some clients and I want the cell format to change to yellow when his date of birth is withing the current and the next monat. In order to do that I wrote these formula in the first and second field of the conditional formating panel=C2=MONTH(TODAY()) and =C2=MONTH(TODAY())+1 , but it doesn't work. Can't you tell me what am I doing wrong Thanks Try this, using only one condition: =OR(MONTH(C2)=MONTH(NOW()),MONTH(C2)=MONTH(NOW())+1) =OR(MONTH(C2)=MONTH(TODAY(),MONTH(C2)+1=MONTH(TODAY()) all in the one CF condition -- HTH RP (remove not...

Proper(text) function on entire worksheet?
Hi, I've acquired a huge Excel (2002) file which I'd like to use in a mailmerge but unfortunately all text entered previously is in uppercase. I've found the function Proper() which corrects uppercase to first letter as a capital then the rest as lowercase, but.... it appears that it can only be used to convert individual cells into a new/adjacent cell (i.e not simply convert the existing cell. Can anyone help me overcome this problem please? Cheers Bill Bill You could use this code and then select the data you wish to convert. You should be aware that Proper does have som...

Fiscal Six-Month Periods
I have a database with a bunch of dates in it. I'd like to create a new calculated field that designates the associated date in a six-month period through a query. The periods go from April 1st to September 30th (1st half of our fiscal year) and then from October 1st to March 31st (2nd half of our fiscal year). So, a date in OBLG_DATE of 6/1/09 would be designated in the new field SFY_SIX as something like 2009S1. A date of 11/13/10 would be designated as something like 2010S2. Thanks for your help to this quite novice user in advance. This expression should calculat...

Senior .NET / C# Developer/Spring.NET | NYC | 6-9 months
Hi, Title: Senior .Net/ C# Developer/Spring.Net Location: NYC Start: Immediate Duration: 6-9 months *****Candidates will be given a written test******** MANDATORY Skill: CANDIDATE MUST HAVE STRONG EXPERIENCE CODING C#, WINFORMS, & XML SPRING.NET EXPERIENCE PREFERRED Technical Skill Requirements: 1. 10+ years experience as a software engineer (planning, designing, coding, unit testing) developing true OO systems. Seasoned developer with min. 4-8 years of experience on a Microsoft platform. 2. Experience building front-end systems with .NET 3. Must have Strong...

Monthly Budgeted amount wrong in MSMoney 2003 deluxe
I have a budget with some life insurance bills in the Insurance : Life category. When I am in the "Enter your expenses section of the budget, It looks like this: Company 1 Every three months 72.28 Company 2 Every three months 64.11 Company 3 Yearly 60.00 Compnay 4 Yearly 45.60 This averages out to 54.26 monthly which is correct. It displays this below these in the Subtotal row,,, BUT,,,,,, When i I look at my budget in the "Review Your Current Budget Status" page for the current month , it shows the actual amount fo...

sumproduct with wildcards?
Hi, will sumproduct() allow the use of wildcards? I'm trying to sum a portion of a large spreadsheet. One column is numerical, the other is text. All of the entries in the text column begin with common values but differ at the end, hence my attempt at the use of a wildcard. But it doesn't seem to work. Any suggestions? thank you. jim -- "If you carve yourself to suit everybody, you'll soon whittle yourself away" No, SUMPRODUCT will not work (directly) with wildcards. You can use something like this: Sum values in column B where the values in column begin wi...

SUMPRODUCT 11-25-09
I the following is a nice formula: =SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)*(D6:D20007=D20012)*(F6:F20007)) Then I wanted to do a SUMPRODUCT() on items beginning with "42" and someone in the newsgroup told me: =SUMPRODUCT((A6:A20010=A20015)*ISNUMBER(MATCH(B20015&"*",B6:B20010,0))*(D6:D20010=D20015)*(F6:F20010)) Where B20015 has "42". I was thinking I would like to expand this a litte and what if I wanted to do a group, for instance: "4100 ; 4200 ; 4300 ; 5115 ; 6050" The data is in A6 to F20007 and what I have done is ...

query by month
Hi, in my query design view, I type in "Month(4)" for my date field in order to get records that were recorded during April, and I know that there are many that have been done in that month, however the query returns no records... What am I doing wrong? Create a new column in your query with the expression: Mth: Month([YourDateField]) Then set the criteria under this column to 4 -- Duane Hookom Microsoft Access MVP "dave.degroot@gmail.com" wrote: > Hi, in my query design view, I type in "Month(4)" for my date field in > order to get records that...

Remove an entire Business Unit and corresponding data from MS CRM database
We created a 2nd installation of MS CRM and copied over our entire database to the new installation. However, we would like to remove/extract all of the activities, leads, contacts, accounts, opportunities that are not connected to Business Unit that the new installation was intended for (for the last 18 mos, I created a parent entity with two child business units.. now one of the child business units will be its own entity with separate licenses, domain, network, users, etc). We probably erroneously copied over the entire database to the new installation of CRM (with disabled users from the...

sumproduct 01-05-10
using this formula =SUMPRODUCT(H18:H103,--(TEXT(B18:B103,"yyyymm")=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+0,1),"yyyymm"))) when column E is mark with "X", not to add the values in column H. Please tell us what you are trying to do. How does column E enter into this? best wsihes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "tleehh" <tleehh@discussions.microsoft.com> wrote in message news:CA44D92F-29BF-4982-93E9-523637B37F2C@microsoft.com... > using this formula > =SUMPRODUCT(H18:H103,--(TEXT(B18:B10...

count wednesdays within a month
I want to count how many let's say wednesdays are within a calendar month and for each month of the year -- sato panago Look here: http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekday -- Kind regards, Niek Otten Microsoft MVP - Excel "sato" <maddoctor@metro.gr> wrote in message news:2F525B96-7111-416D-9903-D02A5ECF1C18@microsoft.com... >I want to count how many let's say wednesdays are within a calendar month >and > for each month of the year > -- > sato panago The general formula is: =4+(DAY(A1-DAY(A1)+35)<WE...

Highlighting entire row and not just cell where cursor is placed
I have a large amount of data and was wondering if there was a way that the entire row would highlight vs just the cell that the cursor is in. For instance, I want the entire row of line 1115 to be highlighted (i.e. to stand out) as opposed to just the cell being highlighted. And, then, if I move to the next row, the entire row would be highltighted instead of just the cell my cursor is in. Thank you for any help. Nick Here is a write-up I have on what you want to do. HTH Otto To have an entire row highlighted to some color if any cell in that row is selected. Note that ...

Sumproduct
When using SUMPRODUCT, is it possible to have the function return a String instead of only numbers? Example: A1:Z1 - Range (1-2) Repeating A2:Z2 - Alphabet (A-Z) A3:Z3 - Cell ID (A3, B3, etc) E1: =SUMPRODUCT(--(1=A1:Z1)*--("A"=A2:Z2)*A3:Z3) If I do my array math correctly, it should be: {1,0,1,0....,1,0} * {1,0,0,0,....,0} * {"A3","B3",...."Z3"} = "A3" -- but instead, I recieve the #VALUE! error. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile:...

What denotes a month in cell format?
I have a list, 3,4,6,7,8,10,12. I want to apply a cell format which will result in months, that is, March, April, June, July, August, October, December. What should be specified? Thanks Not possible using formats, you would need a formula like =INDEX({"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},MATCH(A1,{1;2;3;4;5;6;7;8;9;10;11;12},0)) but it is not possible to format a cell with 10 to return October ...

Select an entire row to copy after finding a value
I am looking for a way to look for a value in a column and after finding that column, selecting that whole row and copying it. Can anyone help If you mean in a macro activecell.entirerow.select If with an Excel shortcut Shift+SpaceBar --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm <steel_curtai@yahoo.com> wrote in message news:1133912738.647332.209000@g14g2000cwa.googlegroups.com... > I am looking for a w...

Current Month Expenses Report vs Budget
I am confused why after I set up my budget and it seems to all be correct that I go to the final screen "Review You Current Budget Status" in MD 2004 and see the line under the budget period of "Expenses: Current Month" budgeted different than what I just set. If I go back though the edit budget my original numbers balance. Where is it getting this number from, which is $1300 more than what I budgeted. I have remade my budget numerous times and still cannot figure out where this number is coming from. Thanks I haven't heard from anyone else who can figure it out eit...

Calculate No. on Months between 2 dates
Hi I need to be able to calculate total no of months between a date range. The first date range will always start at the beginning of a month, and the second will always end at the end of a month, ie; Start Date = 01/01/03 End Date = 01/06/09 I need to know how many months there are between the start, and the end date...any ideas guys...? Thanks >The first date range will always start at >the beginning of a month, and the second will always end >at the end of a month, ie; You mean except in your example. Start is in A1 End is in B1 =Datedif(a1,b1,"m") -- Regard...

how can I make excel worksheet look like a month?
how to insert a month or week on worksheet? help anyone? Google is your friend Excel:calendar -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Amber" <Amber@discussions.microsoft.com> wrote in message news:6EC57BBB-48A5-4232-8C05-1F346A751A98@microsoft.com... > how to insert a month or week on worksheet? > help anyone? ...

OLAP cubes with Time scale : only 6 months
Hi, I built several OLAP cubes views (Data analysis) with the dimension Time. I built the cube with a date range from 1st january to 31st December 2010. In some views I can display the values for the 12 months of the year. For some views, I can just display the first 6 months (2 quarters) while I selected all the 12 months. What could be the issue? Thanks Xiao: It would really help if you were a bit more specific when you post, such as naming the cubes you're working with and specifying the version of the software you're using, so I' have to guess a little t...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

In Excel, look at a date field and return just the month..
I'm looking for an excel formula that looks at a cell containing a date and returns just the month of the date. Hi KC here's some ideas for you try =MONTH(A1) or =A1 and then choose format / cell and in custom type mmm or type mmmm or =text(A1, "mmm") -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "KC" <KC@discussions.microsoft.com> wrote in message news:4A53164A-FB37-460D-A38C-B0F021D890C8@microsoft.com... > I'm looking for an excel formula that looks at a cell containing a date > and &g...

Sumproduct help please
A B C 10/31/2003 10:50:06 AM Chris Grider 59.95 10/31/2003 12:05:26 PM John Dill 101.25 11/01/2003 12:02:20 PM Chris Grider 72.25 Ok I desperatley need help I just dont seem to be able to think this one thru this is what I have so far =SUMPRODUCT((A1:A3="1/1/2003")*(B1:B3="Chris Grider")*C1:C3) What I need it to do is when its 10/31/03 and the tech is Chris Grider then sum column c --- Message posted from http://www.ExcelForum.com/ Try this form =SUMPR...