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

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

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

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!

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

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