Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need More Help on Dates to Months
Posted before but I think I have it slightly figured out. I tried a Pivot
Table but didn't work the way I wanted it too. What I am trying to do is total up the amount of an item by month and also name. For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So far what I have come up with is =SUMPRODUCT((C1:C10="Josh")*(A1:A10="January")*(B1 :B10)) I need to know how to get excel to look at my Dates mm/day/year and put them all in a month catagory so it will sum up the totals for January. How do I get excel to recognize my dates as a full month? Can I add something into that equation? Because that equation works if I just have the Date as a month name...but I don't want to do it like that. Please Help! |
#2
|
|||
|
|||
Hi
personally, i'ld go back to the pivot table option as your data is perfect for it - try 1) click in your data 2) choose pivot table & pivot chart report - next 3) check the range - next 4) new worksheet - finish 5) now drag the dates to where it says "row", drag the Sell to where it says "column" and the units sold to the "data" bit 6) now right mouse click on a date and choose GROUP and Show Detail 7) from the list choose months, hold down the control key and choose years (if your data goes over more than one year) 8) OK does this give you what you want? if not then use the following SUMPRODUCT formula =SUMPRODUCT(--(C1:C10="Josh"),--(TEXT(A1:A10,"mmmm")="January"),B1:B10) Check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html for more details on the SUMPRODUCT function Cheers JulieD "Xandlyn" wrote in message ... Posted before but I think I have it slightly figured out. I tried a Pivot Table but didn't work the way I wanted it too. What I am trying to do is total up the amount of an item by month and also name. For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So far what I have come up with is =SUMPRODUCT((C1:C10="Josh")*(A1:A10="January")*(B1 :B10)) I need to know how to get excel to look at my Dates mm/day/year and put them all in a month catagory so it will sum up the totals for January. How do I get excel to recognize my dates as a full month? Can I add something into that equation? Because that equation works if I just have the Date as a month name...but I don't want to do it like that. Please Help! |
#3
|
|||
|
|||
Hi!
Try this: =SUMPRODUCT(--(ISNUMBER(A1:A6)),--(MONTH(A1:A6)=1),-- (C1:C6="Josh"),B1:B6) OR, use cells to hold your conditions: D1 = 1 (month number for JAN) E1 = Josh =SUMPRODUCT(--(ISNUMBER(A1:A6)),--(MONTH(A1:A6)=D1),-- (C1:C6=E1),B1:B6) Biff -----Original Message----- Posted before but I think I have it slightly figured out. I tried a Pivot Table but didn't work the way I wanted it too. What I am trying to do is total up the amount of an item by month and also name. For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So far what I have come up with is =SUMPRODUCT((C1:C10="Josh")*(A1:A10="January")*(B 1:B10)) I need to know how to get excel to look at my Dates mm/day/year and put them all in a month catagory so it will sum up the totals for January. How do I get excel to recognize my dates as a full month? Can I add something into that equation? Because that equation works if I just have the Date as a month name...but I don't want to do it like that. Please Help! . |
#4
|
|||
|
|||
Close
=SUMPRODUCT(--(C1:C10="Josh"),--(TEXT(A1:A10,"mmm")="Jan"),(B1:B10)) -- HTH RP (remove nothere from the email address if mailing direct) "Xandlyn" wrote in message ... Posted before but I think I have it slightly figured out. I tried a Pivot Table but didn't work the way I wanted it too. What I am trying to do is total up the amount of an item by month and also name. For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So far what I have come up with is =SUMPRODUCT((C1:C10="Josh")*(A1:A10="January")*(B1 :B10)) I need to know how to get excel to look at my Dates mm/day/year and put them all in a month catagory so it will sum up the totals for January. How do I get excel to recognize my dates as a full month? Can I add something into that equation? Because that equation works if I just have the Date as a month name...but I don't want to do it like that. Please Help! |
#5
|
|||
|
|||
On Fri, 11 Mar 2005 23:31:01 -0800, "Xandlyn"
wrote: Posted before but I think I have it slightly figured out. I tried a Pivot Table but didn't work the way I wanted it too. What I am trying to do is total up the amount of an item by month and also name. For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So far what I have come up with is =SUMPRODUCT((C1:C10="Josh")*(A1:A10="January")*(B 1:B10)) I need to know how to get excel to look at my Dates mm/day/year and put them all in a month catagory so it will sum up the totals for January. How do I get excel to recognize my dates as a full month? Can I add something into that equation? Because that equation works if I just have the Date as a month name...but I don't want to do it like that. Please Help! Is this the result you want: Sum of Units Sold Seller: Dates: Josh Rick Grand Total Jan 18 7 25 Feb 1 13 14 Mar 25 25 Grand Total 19 45 64 I got by setting up a simple Pivot Table. I dragged dates to the rows; seller to the columns, and Units Sold to the data area. I then right-clicked on the dates column in the pivot table; selected Group and by Months. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to subtract 2 dates and display the result as months | Excel Worksheet Functions | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) | |||
Difference between two dates in months with decimals | Excel Worksheet Functions | |||
Difference between two dates in months with decimals | Excel Worksheet Functions | |||
Difference between two dates in months with decimals | Excel Worksheet Functions |