Sumproduct and Date format

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

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

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
Click to expand...

Hi Mike,

Yes you're right. My boo boo. Thank you Sir!
 
replace
(a2:a1000="december")
with:
(MONTH(A1:A1000)=12)
Click to expand...

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,

this would now sum Tuesdays for the month of December

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

Mike
 
Hi,

this would now sum Tuesdays for the month of December

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

Mike
Click to expand...

Hi Mike, Thats really neat! Thank you so much for your help! So
weekdays start on Sunday as (1) Saturday as (7)
 
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)
 
Back
Top