Add up a value that meets 2 criteria
 

I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16 pencils.

Add up a value that meets 2 criteria
 

Use SUMPRODUCT:

=SUMPRODUCT(--(D1:D10="Jacob"),--(B1:B10="Pencil"),C1:C10)

HTH,
Paul

--

"Lee" wrote in message
...
I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.

Add up a value that meets 2 criteria
 

=sumproduct(--(B1:B100=Pencil"),--(D1:D100="Jacob"),C1:C100)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lee" wrote in message
...
I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.

Add up a value that meets 2 criteria
 

Assume Dates (for multiple months) in cells A2:A500, product in cells
B2:B500, amount in cells C2:C500, and salesperson in cells D2:D500
Criteria would be in cells F2 for date (entered as mm/1/yy), F3 for product,
and F4 for salesperon.
=SUMPRODUCT(--(YEAR(A2:A500)=YEAR(F2)),--(MONTH(A2:A500)=MONTH(F2)),--(B2:B500=F3),--(D2:D500=F4),(C2:C500))
--
** John C **

"PCLIVE" wrote:

Use SUMPRODUCT:

=SUMPRODUCT(--(D1:D10="Jacob"),--(B1:B10="Pencil"),C1:C10)

HTH,
Paul

--

"Lee" wrote in message
...
I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.

Add up a value that meets 2 criteria
 

It is showing a "#VALUE!"

"John C" wrote:

Assume Dates (for multiple months) in cells A2:A500, product in cells
B2:B500, amount in cells C2:C500, and salesperson in cells D2:D500
Criteria would be in cells F2 for date (entered as mm/1/yy), F3 for product,
and F4 for salesperon.
=SUMPRODUCT(--(YEAR(A2:A500)=YEAR(F2)),--(MONTH(A2:A500)=MONTH(F2)),--(B2:B500=F3),--(D2:D500=F4),(C2:C500))
--
** John C **

"PCLIVE" wrote:

Use SUMPRODUCT:

=SUMPRODUCT(--(D1:D10="Jacob"),--(B1:B10="Pencil"),C1:C10)

HTH,
Paul

--

"Lee" wrote in message
...
I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.

Add up a value that meets 2 criteria
 

It sounds as if at least one of the cells the relevant ranges has text,
rather than a number. Check for spaces, non-breaking spaces [CHAR(160)],
and other non-printing characters.
--
David Biddulph

"Lee" wrote in message
...
It is showing a "#VALUE!"

"John C" wrote:

Assume Dates (for multiple months) in cells A2:A500, product in cells
B2:B500, amount in cells C2:C500, and salesperson in cells D2:D500
Criteria would be in cells F2 for date (entered as mm/1/yy), F3 for
product,
and F4 for salesperon.
=SUMPRODUCT(--(YEAR(A2:A500)=YEAR(F2)),--(MONTH(A2:A500)=MONTH(F2)),--(B2:B500=F3),--(D2:D500=F4),(C2:C500))
--
** John C **

"PCLIVE" wrote:

Use SUMPRODUCT:

=SUMPRODUCT(--(D1:D10="Jacob"),--(B1:B10="Pencil"),C1:C10)

HTH,
Paul

--

"Lee" wrote in message
...
I have an inventory list where different people check out certain
products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.

Add up a value that meets 2 criteria
 

Can you copy/paste your exact formula?
Also, have you verified that all dates are actually entered as dates?
--
** John C **

"Lee" wrote:

It is showing a "#VALUE!"

"John C" wrote:

Assume Dates (for multiple months) in cells A2:A500, product in cells
B2:B500, amount in cells C2:C500, and salesperson in cells D2:D500
Criteria would be in cells F2 for date (entered as mm/1/yy), F3 for product,
and F4 for salesperon.
=SUMPRODUCT(--(YEAR(A2:A500)=YEAR(F2)),--(MONTH(A2:A500)=MONTH(F2)),--(B2:B500=F3),--(D2:D500=F4),(C2:C500))
--
** John C **

"PCLIVE" wrote:

Use SUMPRODUCT:

=SUMPRODUCT(--(D1:D10="Jacob"),--(B1:B10="Pencil"),C1:C10)

HTH,
Paul

--

"Lee" wrote in message
...
I have an inventory list where different people check out certain products.
I need to be able to have a formula that adds the value of a certain
product
for a certain salesman. This is an example of what I have.

Date Product Amount salesperson
10-Oct Pencil 5 Jacob
15-Oct Pen 10 Brian
16-Oct Paper 2 Brian
20-Oct Pencil 11 Jacob
25-Oct Pencil 7 Brian

and i want it to show that for this month Jacob sold a total of 16
pencils.