Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a Value IF a cell meets a criteria | Excel Discussion (Misc queries) | |||
Do Nothing If Meets Criteria | Excel Discussion (Misc queries) | |||
How do I go to the next cell that meets criteria | Excel Discussion (Misc queries) | |||
SUMIF meets a criteria | Excel Worksheet Functions | |||
Max value that meets a criteria | Excel Worksheet Functions |