Need More Help on Dates to Months

X

Xandlyn

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!
 
J

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
 
B

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
 
B

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)
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top