| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with 2 creteria one Name one DAte
A1_A5 Name Brian, Chris, Brian , Chris, Brian
B1_B5 Date 1/1/2006, 2/15/2006, 1/15/2006, 2/29/2006, 2/3/2006 C1_C5 $ 100, 150 , 250, 95, 10 I Need How many times Brian sold somthin in january (2) or Chris (0) in february Chris (2) brian (1) How many Money January Brian (350) or Chris (0) in february Chris (245) brian (10) i like to use sumif ??? for the money and countif ????? for Sales but i dnt know the formulars. Ineed help Thank You Rolf |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with 2 creteria one Name one DAte
Using your example data:
How many sales Brian had in January (2): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1)) The dollar value of those sales ($350): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1),C1:C5) Modify as needed. Note 2/29/06 is not a valid date. "Rolf" wrote: A1_A5 Name Brian, Chris, Brian , Chris, Brian B1_B5 Date 1/1/2006, 2/15/2006, 1/15/2006, 2/29/2006, 2/3/2006 C1_C5 $ 100, 150 , 250, 95, 10 I Need How many times Brian sold somthin in january (2) or Chris (0) in february Chris (2) brian (1) How many Money January Brian (350) or Chris (0) in february Chris (245) brian (10) i like to use sumif ??? for the money and countif ????? for Sales but i dnt know the formulars. Ineed help Thank You Rolf |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with 2 creteria one Name one DAte
Seeing as you have different years, you might want to include the year test
=SUMPRODUCT(--(A1:A5="Brian"),--(YEAR(B1:B5)=2006),--(MONTH(B1:B5)=1),C1:C5) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMB" wrote in message ... Using your example data: How many sales Brian had in January (2): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1)) The dollar value of those sales ($350): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1),C1:C5) Modify as needed. Note 2/29/06 is not a valid date. "Rolf" wrote: A1_A5 Name Brian, Chris, Brian , Chris, Brian B1_B5 Date 1/1/2006, 2/15/2006, 1/15/2006, 2/29/2006, 2/3/2006 C1_C5 $ 100, 150 , 250, 95, 10 I Need How many times Brian sold somthin in january (2) or Chris (0) in february Chris (2) brian (1) How many Money January Brian (350) or Chris (0) in february Chris (245) brian (10) i like to use sumif ??? for the money and countif ????? for Sales but i dnt know the formulars. Ineed help Thank You Rolf |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF with 2 creteria one Name one DAte
I don't see two different years, but if it is possible, then the OP should
surely include the year test. "Bob Phillips" wrote: Seeing as you have different years, you might want to include the year test =SUMPRODUCT(--(A1:A5="Brian"),--(YEAR(B1:B5)=2006),--(MONTH(B1:B5)=1),C1:C5) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMB" wrote in message ... Using your example data: How many sales Brian had in January (2): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1)) The dollar value of those sales ($350): =SUMPRODUCT(--(A1:A5="Brian"),--(MONTH(B1:B5)=1),C1:C5) Modify as needed. Note 2/29/06 is not a valid date. "Rolf" wrote: A1_A5 Name Brian, Chris, Brian , Chris, Brian B1_B5 Date 1/1/2006, 2/15/2006, 1/15/2006, 2/29/2006, 2/3/2006 C1_C5 $ 100, 150 , 250, 95, 10 I Need How many times Brian sold somthin in january (2) or Chris (0) in february Chris (2) brian (1) How many Money January Brian (350) or Chris (0) in february Chris (245) brian (10) i like to use sumif ??? for the money and countif ????? for Sales but i dnt know the formulars. Ineed help Thank You Rolf |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| how to use sumif function to check date in 1 sheet is < 2 sheet | Excel Worksheet Functions | |||
| Another Date issue. | Excel Worksheet Functions | |||
| sumif using today's date | Excel Discussion (Misc queries) | |||
Linear Mode
