Date Format and Sumproduct


W

wx4usa

I have a column A for the date MM/DD/YYYY format.

I need to have a sumproduct formula look thru the database and return
sales totals for salesperson by month, day and year separately.

For example:
Column
A is date
B is salesperson
C is sales

On a separate page in the workbook, I have a report with these
selection criteria...
A1 is salesperson
A2 is month
A3 is year

I need a formula to return sales for this salesperson for the month
and for the year. Looking ahead, the report/database will span
several years.

Also, is there a way to sumproduct sales by day of the week such as
Tuesdays using the mm/dd/yyyy format?

I seem to struggle with dates and sumproduct.

Any help would be greatly appreciated.
 
Ad

Advertisements

T

T. Valko

On a separate page in the workbook, I have
a report with these selection criteria...
A1 is salesperson
A2 is month
A3 is year

In A2, how are you entering the month, as the month number or the month
name?

A2 = 5 (month 5 = May)
A2 = May
 
T

T. Valko

Try these...

Sum by salesperson for month and year:

=SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C20)

Sum for a specific weekday such as Tuesday:

=SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20)

Or:

=SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20)

Where n = the weekday number:

1 = Sun
2 = Mon
3 = Tue
4 = Wed
5 = Thu
6 = Fri
7 = Sat

--
Biff
Microsoft Excel MVP


In A2, how are you entering the month, as the month number or the month
name?

A2 = 5 (month 5 = May)
A2 = May

I am entering Month Name such as May.
 
W

wx4usa

Try these...

Sum by salesperson for month and year:

=SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C20)

Sum for a specific weekday such as Tuesday:

=SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20)

Or:

=SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20)

Where n = the weekday number:

1 = Sun
2 = Mon
3 = Tue
4 = Wed
5 = Thu
6 = Fri
7 = Sat

--
Biff
Microsoft Excel MVP





I am entering Month Name such as May.

Hi Biff

How can I also do a specific day such as Dec 1, 2009? 12/01/2009?

I tried this and it doesnt work.....
=SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A
$3&A4),$C$5:$C$21)

where December is in a2, 1 is on a3 and 2009 is in a4
 
Ad

Advertisements

T

T. Valko

It would be a lot easier if you were to just enter the whole data (as a
date) in one cell:

A2 = 12/1/2009 (or 1/12/2009 depending on your local date format)

=SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21)

--
Biff
Microsoft Excel MVP


Try these...

Sum by salesperson for month and year:

=SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C20)

Sum for a specific weekday such as Tuesday:

=SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20)

Or:

=SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20)

Where n = the weekday number:

1 = Sun
2 = Mon
3 = Tue
4 = Wed
5 = Thu
6 = Fri
7 = Sat

--
Biff
Microsoft Excel MVP





I am entering Month Name such as May.

Hi Biff

How can I also do a specific day such as Dec 1, 2009? 12/01/2009?

I tried this and it doesnt work.....
=SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A
$3&A4),$C$5:$C$21)

where December is in a2, 1 is on a3 and 2009 is in a4
 
W

wx4usa

It would be a lot easier if you were to just enter the whole data (as a
date) in one cell:

A2 = 12/1/2009 (or 1/12/2009 depending on your local date format)

=SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21)

--
Biff
Microsoft Excel MVP








Hi Biff

How can I also do a specific day such as Dec 1, 2009?  12/01/2009?

I tried this and it doesnt work.....
=SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A
$3&A4),$C$5:$C$21)

where December is in a2, 1 is on a3 and 2009 is in a4

Hey Biff, Can I also return sales by my specific salesperson for a
date range such as 11/15/2009 thru 12/20/2009?
 
Ad

Advertisements

T

T. Valko

Use cells to hold the date boundaries:

A1 = salesperson
A2 = start date
A3 = end date

=SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21>=$A$2),--($A$5:$A$21<=$A$3),$C$5:$C$21)

--
Biff
Microsoft Excel MVP


It would be a lot easier if you were to just enter the whole data (as a
date) in one cell:

A2 = 12/1/2009 (or 1/12/2009 depending on your local date format)

=SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21)

--
Biff
Microsoft Excel MVP








Hi Biff

How can I also do a specific day such as Dec 1, 2009? 12/01/2009?

I tried this and it doesnt work.....
=SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A
$3&A4),$C$5:$C$21)

where December is in a2, 1 is on a3 and 2009 is in a4

Hey Biff, Can I also return sales by my specific salesperson for a
date range such as 11/15/2009 thru 12/20/2009?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top