Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 122
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 122
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 122
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct mmddyyy just the month? wx4usa Excel Discussion (Misc queries) 2 January 8th 10 01:47 AM
sumproduct calculation does not calculate entire range John Gregory Excel Discussion (Misc queries) 5 September 11th 09 09:16 PM
SUMPRODUCT - Using Month Phendrena Excel Worksheet Functions 15 August 18th 08 07:15 PM
SUMPRODUCT - Using Month Jarek Kujawa[_2_] Excel Worksheet Functions 0 July 15th 08 11:56 AM
Problem with sumproduct and month=1 bobh727 Excel Worksheet Functions 6 February 15th 05 08:13 AM


All times are GMT +1. The time now is 10:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"