Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
Help !!! I need to find out how many sales someone did in say Jan. I've got
this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
You could use this instead of what you posted...
=SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880)) but I would note that this does not include a conditional test for an individual sales person (which your original question seemed to indicate might be your goal). Rick "Chris" wrote in message ... Help !!! I need to find out how many sales someone did in say Jan. I've got this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
Unless you have some values in L2 that are less than zero, then you don't
have to inclued the "0" part. =SUMPRODUCT(--(MONTH(D2:D188)=1),--(YEAR(D2:D188)=2008),--(L2:L188)) If you do have values less than zero, then: =SUMPRODUCT(--(MONTH(D2:D188)=1),--(YEAR(D2:D188)=2008),--(L2:L1880),(L2:L188)) HTH, Paul -- "Chris" wrote in message ... Help !!! I need to find out how many sales someone did in say Jan. I've got this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
=SUMPRODUCT((TEXT(D2:D188,"mm/yyyy")="01/2008")*(L2:L1880))
"Chris" wrote: Help !!! I need to find out how many sales someone did in say Jan. I've got this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
Thanks for your quick responses, I will put in the salespersons name
J2:J188="Anthony", the 0 argument is to check that there's an amount in the field... "Rick Rothstein (MVP - VB)" wrote: You could use this instead of what you posted... =SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880)) but I would note that this does not include a conditional test for an individual sales person (which your original question seemed to indicate might be your goal). Rick "Chris" wrote in message ... Help !!! I need to find out how many sales someone did in say Jan. I've got this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the month
You might want to consider one of the other two formulas instead of mine...
those both account for the year as well as the month (it might not important now, but may be when you get to next January). Rick "Chris" wrote in message ... Thanks for your quick responses, I will put in the salespersons name J2:J188="Anthony", the 0 argument is to check that there's an amount in the field... "Rick Rothstein (MVP - VB)" wrote: You could use this instead of what you posted... =SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880)) but I would note that this does not include a conditional test for an individual sales person (which your original question seemed to indicate might be your goal). Rick "Chris" wrote in message ... Help !!! I need to find out how many sales someone did in say Jan. I've got this so far but it won't work with the date field.. =SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880)) Many thanks in advance.. Opened Sales Person Jan Feb 08-Jan-08 Gary £1,189.11 £2,437.42 19-Dec-07 Anthony £792.81 £2,082.08 19-Dec-07 Anthony £414.06 £409.35 23-Jan-08 Stephen £276.00 £419.38 29-Jan-08 Bobby £298.15 £529.62 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the MIN of Month and Day only | Excel Discussion (Misc queries) | |||
I would like to find the last day of a month | Excel Worksheet Functions | |||
How do i find the first value every month | Excel Discussion (Misc queries) | |||
Find out first Friday every month | Excel Discussion (Misc queries) | |||
Find out first Friday every month | Excel Worksheet Functions |