Weekday

[Guest]

I am trying to sum items by weekday from date. My list has dates and items
recieved. I would like to total all items received on Mondays, then all
items Tuesdays etc. Sorting is not an option since I looking up the
information on a different sheet based on the criteria that it is what day of
the week? Any sugestions?

 

[Dave Peterson]

I had my dates in A2:A30.
I had my quantities in B2:B30.

I put Sunday, Monday, ..., Saturday in 7 cells (I used C2:C8).

Then in D2, I put this:
=SUMPRODUCT(--($A$2:$A$30<>""),--(TEXT($A$2:$A$30,"dddd")=$C2),--($B$2:$B$30))

Adjust the range to match--but you can't use the whole column.

Then drag down through D8.

=sumproduct() likes to work with numbers. The -- stuff turns true/falses to 1's
and 0's.

 

[Roger Govier]

Hi Thomas

One way would be with Sumproduct.
With Dates in column A and values in column B
=SUMPRODUCT(--(Weekday($A$1:$A$100)=2),$B$1:$B$100)
This would give the result for Monday = 2

Better still, set up a list of cells in say D17 and change formula to
=SUMPRODUCT(--(Weekday($A$1:$A$100)=D1),$B$1:$B$100)
and enter in E1 and copy down to E7

 

[Ron Rosenfeld]

I am trying to sum items by weekday from date. My list has dates and items
recieved. I would like to total all items received on Mondays, then all
items Tuesdays etc. Sorting is not an option since I looking up the
information on a different sheet based on the criteria that it is what day of
the week? Any sugestions?

=SUMPRODUCT((WEEKDAY(date_rng)=DOW)*(item_rcvd_rng))

For DOW

1=Sunday
2=Monday
3=Tuesday
etc.

Make sure your two ranges (*_rng) in the formula are the same size.


--ron

 

[Guest]

Add a column and insert the WEEKDAY() function in the new column, then use
SUMIF() to look for each day. WEEKDAY() will return a number. Then insert 7
SUMIF() formulas to look for each of the 7 days.

Insert the functions using the 'Insert' - 'Function' menu and the wizard
will walk you through setting up the functions correctly.

 

[daddylonglegs]

If you have dates in A1:A100 and associated amounts in B1:B100 this
formula will sum all amounts associated with Mondays

=SUMPRODUCT(--(WEEKDAY(A1:A100)=2),B1:B100)

For other days replace 2 with...

1 for Sunday
3 for Tuesday
4 for Wednesday etc.

 

[George Carlin]

Hi Thomas!

You got lots great replies and I wanted to add my own!

G