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?

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.

Thomas wrote:

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

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 D1:D7 and change formula to
=SUMPRODUCT(--(Weekday($A$1:$A$100)=D1),$B$1:$B$100)
and enter in E1 and copy down to E7

--
Regards

Roger Govier


"Thomas" wrote in message
...
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?

On Sat, 28 Jan 2006 12:44:27 -0800, Thomas
wrote:

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

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.
--
George


"Thomas" wrote:

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?

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.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506061

Hi Thomas!

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

G

"Thomas" wrote in message
...
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?