Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
In A1 I have the date: mmddyyy. I need a sumproduct formula to return
the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
Did you check your earlier post for the answer
-- Don Guillett Microsoft MVP Excel SalesAid Software "wx4usa" wrote in message ... In A1 I have the date: mmddyyy. I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
Try this...
=SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... In A1 I have the date: mmddyyy. I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
On Jan 8, 1:24*pm, "T. Valko" wrote:
Try this... =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... In A1 I have the date: mmddyyy. *I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale I did check earlier post, but I could not figure out how to add the year and the month from mmddyyy. Biff's answer did it! Thanks Biff! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
On Jan 8, 1:24*pm, "T. Valko" wrote:
Try this... =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... In A1 I have the date: mmddyyy. *I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale Hi Biff, Me again =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),-- (B2:B1000="salesperson"),C2:C1000) How do I make the above formula work for a date range? 11/01/2009 to 01/31/2010 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct entire month from mmddyyy?
Use cells to hold the date boundaries:
A1 = start date = 11/01/2009 B1 = end date = 01/31/2010 =SUMPRODUCT(--(A2:A1000=A1),--(A2:A1000<=B1),--(B2:B1000="salesperson"),C2:C1000) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Jan 8, 1:24 pm, "T. Valko" wrote: Try this... =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),--(B2:B1000="salesperson"),C2:C1000) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... In A1 I have the date: mmddyyy. I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale Hi Biff, Me again =SUMPRODUCT(--(TEXT(A2:A1000,"myyyy")=text(A1,"myyyy")),-- (B2:B1000="salesperson"),C2:C1000) How do I make the above formula work for a date range? 11/01/2009 to 01/31/2010 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct mmddyyy just the month? | Excel Discussion (Misc queries) | |||
sumproduct calculation does not calculate entire range | Excel Discussion (Misc queries) | |||
SUMPRODUCT - Using Month | Excel Worksheet Functions | |||
SUMPRODUCT - Using Month | Excel Worksheet Functions | |||
Problem with sumproduct and month=1 | Excel Worksheet Functions |