Weekday

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" <[email protected]> wrote in message
news:[email protected]...
>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 <[email protected]>
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?

Hi Thomas!

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

G

"Thomas" <[email protected]> wrote in message
news:[email protected]...
>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?