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$30lt;gt;quot;quot;),--(TEXT($A$2:$A$30,quot;ddddquot;)=$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:
gt;
gt; I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day of
gt; 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 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
--
Regards
Roger Govierquot;Thomasquot; gt; wrote in message
...
gt;I am trying to sum items by weekday from date. My list has dates and
gt;items
gt; recieved. I would like to total all items received on Mondays, then
gt; all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what
gt; day of
gt; the week? Any sugestions?
On Sat, 28 Jan 2006 12:44:27 -0800, Thomas gt;
wrote:
gt;I am trying to sum items by weekday from date. My list has dates and items
gt;recieved. I would like to total all items received on Mondays, then all
gt;items Tuesdays etc. Sorting is not an option since I looking up the
gt;information on a different sheet based on the criteria that it is what day of
gt;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.
--
Georgequot;Thomasquot; wrote:
gt; I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day of
gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506061Hi Thomas!
You got lots great replies and I wanted to add my own!
G
quot;Thomasquot; gt; wrote in message
...
gt;I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day
gt; of
gt; the week? Any sugestions?
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$30lt;gt;quot;quot;),--(TEXT($A$2:$A$30,quot;ddddquot;)=$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:
gt;
gt; I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day of
gt; 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 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
--
Regards
Roger Govierquot;Thomasquot; gt; wrote in message
...
gt;I am trying to sum items by weekday from date. My list has dates and
gt;items
gt; recieved. I would like to total all items received on Mondays, then
gt; all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what
gt; day of
gt; the week? Any sugestions?
On Sat, 28 Jan 2006 12:44:27 -0800, Thomas gt;
wrote:
gt;I am trying to sum items by weekday from date. My list has dates and items
gt;recieved. I would like to total all items received on Mondays, then all
gt;items Tuesdays etc. Sorting is not an option since I looking up the
gt;information on a different sheet based on the criteria that it is what day of
gt;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.
--
Georgequot;Thomasquot; wrote:
gt; I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day of
gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506061Hi Thomas!
You got lots great replies and I wanted to add my own!
G
quot;Thomasquot; gt; wrote in message
...
gt;I am trying to sum items by weekday from date. My list has dates and items
gt; recieved. I would like to total all items received on Mondays, then all
gt; items Tuesdays etc. Sorting is not an option since I looking up the
gt; information on a different sheet based on the criteria that it is what day
gt; of
gt; the week? Any sugestions?
