Need More Help on Dates to Months

[Guest]

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!

 

[JulieD]

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

 

[Biff]

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

 

[Bob Phillips]

Close

=SUMPRODUCT(--(C1:C10="Josh"),--(TEXT(A1:A10,"mmm")="Jan"),(B1:B10))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

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!

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