I need some major help. I have 4 columns.
A B C
D
3/1/2005 Bike John
Amount

a is date
b is product
c is salesman
d is amount

I need to write a formula and come up with a chart that will show how
many bikes that john sold, each month.

I thought it would be a lot simpler, and I am trying to educate
myself.
I have played with SumIF and SumProduct, but I dont think sumif
supports multiple criteria ranges.

I am a major noob when it comes to writing formulas. I could build you
a samba server, and build a website, but I cant seem to do this at
all.

I bet this is easy for some of you guys out there. Can you lend me a
hand????


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile: http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707

Why not use a pivot table?

http://peltiertech.com/Excel/Pivots/pivotstart.htm


otherwise use

=SUMPRODUCT(--(MONTH(A2:A200)=7),--(B2:B200="Bike"),--(C2:C200="Mike"),D2:D200)

assuming you want to count the numbers in D where A is a particular month
(in this example July), B is bike and C is Mike

--
Regards,

Peo Sjoblom

(No private emails please)


"jshafer817" wrote
in message ...

I need some major help. I have 4 columns.
A B C
D
3/1/2005 Bike John
Amount

a is date
b is product
c is salesman
d is amount

I need to write a formula and come up with a chart that will show how
many bikes that john sold, each month.

I thought it would be a lot simpler, and I am trying to educate
myself.
I have played with SumIF and SumProduct, but I dont think sumif
supports multiple criteria ranges.

I am a major noob when it comes to writing formulas. I could build you
a samba server, and build a website, but I cant seem to do this at
all.

I bet this is easy for some of you guys out there. Can you lend me a
hand????


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile:
http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707

Hi,

Use

=SUMPRODUCT((MONTH(A1:A100)=3)*(B1:B100="Bike")*(C 1:C100="John")*(D1:D100))

This formula would give you the bike sales by john for a particular
month. To find out the bike sales for other months, change the month
from 3 to the month you need.

Alternatively, you can make a pivot table, group the dates by months and
then do a pivot chart based on that.

Regards

Govind.

jshafer817 wrote:

I need some major help. I have 4 columns.
A B C
D
3/1/2005 Bike John
Amount

a is date
b is product
c is salesman
d is amount

I need to write a formula and come up with a chart that will show how
many bikes that john sold, each month.

I thought it would be a lot simpler, and I am trying to educate
myself.
I have played with SumIF and SumProduct, but I dont think sumif
supports multiple criteria ranges.

I am a major noob when it comes to writing formulas. I could build you
a samba server, and build a website, but I cant seem to do this at
all.

I bet this is easy for some of you guys out there. Can you lend me a
hand????

As an example, let's say your data is in cells A1:D4.

You could create a table with month number along the left side and names
across the top (in my test I created this table in cells A10:C14)

John Matt
1 X
2
3
4
5
6


Then enter a formula like this, copy across and down (I entered it in cell
B11, where the "X" is above):

=SUMPRODUCT((MONTH($A$1:$A$4)=$A13)*($C$1:$C$4=B$1 0)*($B$1:$B$4="Bike"))

To get the value of the bikes sold, you could add

*($D$1:$D$4)

at the end of the formula (inside the last parenthesis).



"jshafer817" wrote:


I need some major help. I have 4 columns.
A B C
D
3/1/2005 Bike John
Amount

a is date
b is product
c is salesman
d is amount

I need to write a formula and come up with a chart that will show how
many bikes that john sold, each month.

I thought it would be a lot simpler, and I am trying to educate
myself.
I have played with SumIF and SumProduct, but I dont think sumif
supports multiple criteria ranges.

I am a major noob when it comes to writing formulas. I could build you
a samba server, and build a website, but I cant seem to do this at
all.

I bet this is easy for some of you guys out there. Can you lend me a
hand????


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile: http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707

a pivot table sounds like it would be your best way to go. Make sure no
blank rows are in your information table. select a cell within the info (or
highlight all the data). Go to the Data menu -- choose Pivot Table And Pivot
Chart Reports. The Wizard should pop up and instruct you through it. This
will make it easier to select information by month, salesman, item, or
combinations of them. This will also create your chart for you.

"jshafer817" wrote:


I need some major help. I have 4 columns.
A B C
D
3/1/2005 Bike John
Amount

a is date
b is product
c is salesman
d is amount

I need to write a formula and come up with a chart that will show how
many bikes that john sold, each month.

I thought it would be a lot simpler, and I am trying to educate
myself.
I have played with SumIF and SumProduct, but I dont think sumif
supports multiple criteria ranges.

I am a major noob when it comes to writing formulas. I could build you
a samba server, and build a website, but I cant seem to do this at
all.

I bet this is easy for some of you guys out there. Can you lend me a
hand????


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile: http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707

=SUM(IF($C$2:$C$12000="DDS1",IF($B$2:$B$12000=2391 ,IF($A$2:$A$12000<=DATEVALUE("3/31/2005"),IF($A$2:$A$12000=DATEVALUE("3/2/2005"),$D$2:$D$12000,0),0),0),0))


Found something called the sum add in or something.

That was cool.


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile: http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707

=sumproduct((datevalue(daterange)=datevalue(x))*( datevalue(daterange)<=datevalue(y))*(salesmanrange ="john")*(productrange="apple")*(amountrange))

where daterange is the column of dates, salesmanrange is the columnof
salesmen, productrange is the column of products, and amountrange is
the column of amounts. All are defined ranges such as a2:a100, not
a:a.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=391707

Cool thanks man! That worked too.

I have been thinking. I would like this data in a mysql database. And
be able to do the exact same query, but with a sql query.

Then display the results on a website, or something...

Anyone have any sql experience. I should probably blow the dust off
some of my sql books and start learning. That and my php book.
'
I know I know, I should be posting this on a SQL forum...

If you guys even need some dental advice, go to
http://www.dentalcom.net

I made it.
:)

The reason I am doing this is to help someone out. We are experimenting
and having some fun. If I could find a SQL way to do it, then I get a
free box of donuts!

LOL.


--
jshafer817
------------------------------------------------------------------------
jshafer817's Profile: http://www.excelforum.com/member.php...o&userid=25758
View this thread: http://www.excelforum.com/showthread...hreadid=391707