Sumproduct and Date format

[wx4usa]

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))

 

[Mike H]

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

 

[Gary''s Student]

replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)

 

[wx4usa]

replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)

Gary, Worked perfectly - Thank you.

 

[wx4usa]

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

Hi Mike,

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

 

[wx4usa]

replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)

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?

 

[Mike H]

Hi,

this would now sum Tuesdays for the month of December

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

Mike

 

[wx4usa]

Hi,

this would now sum Tuesdays for the month of December

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

Mike

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

 

[Chip Pearson]

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)