Sumproduct and Date format
 

How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Sumproduct and Date format
 

Hi,

I'm sure it's a typo but your ranges must be the same size. Try this

=SUMPRODUCT((MONTH(A1:A1000)=12)*(B1:B1000))

Mike

"wx4usa" wrote:

How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Sumproduct and Date format
 

replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k


"wx4usa" wrote:

How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Sumproduct and Date format
 

On Dec 13, 8:35*am, Gary''s Student
wrote:
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Gary, Worked perfectly - Thank you.

Sumproduct and Date format
 

On Dec 13, 8:34*am, Mike H wrote:
Hi,

I'm sure it's a typo but your ranges must be the same size. Try this

=SUMPRODUCT((MONTH(A1:A1000)=12)*(B1:B1000))

Mike

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Hi Mike,

Yes you're right. My boo boo. Thank you Sir!

Sumproduct and Date format
 

On Dec 13, 8:35*am, Gary''s Student
wrote:
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Hi Gary's Student

Can I have the sumproduct formula allso look at the mm/dd/yyyy date
format in column A and return just specific weekdays such as Tuesdays?

Sumproduct and Date format
 

Hi,

this would now sum Tuesdays for the month of December

=SUMPRODUCT((MONTH(A1:A1000)=12)*(WEEKDAY(A1:A1000 )=3)*(B1:B1000))

Mike

"wx4usa" wrote:

On Dec 13, 8:35 am, Gary''s Student
wrote:
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Hi Gary's Student

Can I have the sumproduct formula allso look at the mm/dd/yyyy date
format in column A and return just specific weekdays such as Tuesdays?

Sumproduct and Date format
 

On Dec 13, 9:58*am, Mike H wrote:
Hi,

this would now sum Tuesdays for the month of December

=SUMPRODUCT((MONTH(A1:A1000)=12)*(WEEKDAY(A1:A1000 )=3)*(B1:B1000))

Mike

"wx4usa" wrote:
On Dec 13, 8:35 am, Gary''s Student
wrote:
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Hi Gary's Student

Can I have the sumproduct formula allso look at the mm/dd/yyyy date
format in column A and return just specific weekdays such as Tuesdays?

Hi Mike, Thats really neat! Thank you so much for your help! So
weekdays start on Sunday as (1) Saturday as (7)

Sumproduct and Date format
 

So weekdays start on Sunday as (1) Saturday as (7)

By default, yes, but that can be changed. See help for details.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 13 Dec 2008 08:28:06 -0800 (PST), wx4usa
wrote:

On Dec 13, 9:58*am, Mike H wrote:
Hi,

this would now sum Tuesdays for the month of December

=SUMPRODUCT((MONTH(A1:A1000)=12)*(WEEKDAY(A1:A1000 )=3)*(B1:B1000))

Mike

"wx4usa" wrote:
On Dec 13, 8:35 am, Gary''s Student
wrote:
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
--
Gary''s Student - gsnu2007k

"wx4usa" wrote:
How do I use sumproduct to return December data from a database if the
range a1:a1000 is formatted mm/dd/yyyy? =sumproduct
((a2:a1000="december")*(b1:b1000))

Hi Gary's Student

Can I have the sumproduct formula allso look at the mm/dd/yyyy date
format in column A and return just specific weekdays such as Tuesdays?

Hi Mike, Thats really neat! Thank you so much for your help! So
weekdays start on Sunday as (1) Saturday as (7)