Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif weekday
I'm trying to sumif different days of the week. Table is setup as below:
A. Date Date Date Date Date ...... B. Data Data Data Data Data ...... I know that =weekday(Date,1) will give me a 1 through 7 of the date Sunday through Saturday. Somehow, I feel like =sumif(A:A,weekday(A:A)=1,sum(B:B)) would give me the sum of all Sundays but it's not quite working. What am I'm doing wrong? Can someone help me out? I don't want to make row A into a static Sun, Mon, Tue. Then it's easy since =sumif(A:A,="Sun",sum(B:B)) would probably work. Thanks so much for all the help in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif weekday
=SUMPRODUCT(--(WEEKDAY(A2:A40)=1),B2:B40)
Adjust your range to suit, you can not use whole column pior to XL-2007 "Sungibungi" wrote: I'm trying to sumif different days of the week. Table is setup as below: A. Date Date Date Date Date ...... B. Data Data Data Data Data ...... I know that =weekday(Date,1) will give me a 1 through 7 of the date Sunday through Saturday. Somehow, I feel like =sumif(A:A,weekday(A:A)=1,sum(B:B)) would give me the sum of all Sundays but it's not quite working. What am I'm doing wrong? Can someone help me out? I don't want to make row A into a static Sun, Mon, Tue. Then it's easy since =sumif(A:A,="Sun",sum(B:B)) would probably work. Thanks so much for all the help in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif weekday
On Tue, 6 Apr 2010 20:19:01 -0700, Teethless mama
wrote: =SUMPRODUCT(--(WEEKDAY(A2:A40)=1),B2:B40) Adjust your range to suit, you can not use whole column pior to XL-2007 If you are using the 1900 date system, and DOW=7, you will also add any values in B2:B40 where A2:A4 is blank. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif weekday
On Tue, 6 Apr 2010 19:29:01 -0700, Sungibungi
wrote: I'm trying to sumif different days of the week. Table is setup as below: A. Date Date Date Date Date ...... B. Data Data Data Data Data ...... I know that =weekday(Date,1) will give me a 1 through 7 of the date Sunday through Saturday. Somehow, I feel like =sumif(A:A,weekday(A:A)=1,sum(B:B)) would give me the sum of all Sundays but it's not quite working. What am I'm doing wrong? Can someone help me out? I don't want to make row A into a static Sun, Mon, Tue. Then it's easy since =sumif(A:A,="Sun",sum(B:B)) would probably work. Thanks so much for all the help in advance. If DOW = Day of the Week (Sun=1, Mon=2, ...), and you are using Excel 2007 or later: =SUMPRODUCT((WEEKDAY(A:A)=DOW)*ISNUMBER(A:A),B:B) If using a version of Excel prior to 2007, then you cannot refer to an entire column. So the maximum range would be: =SUMPRODUCT((WEEKDAY(A1:A65535)=DOW)*ISNUMBER(A1:A 65535),B1:B65535) However, it would be best to either make the range dynamic, or have it small enough to always encompass the maximum number of entries. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekday and Sumif | Excel Discussion (Misc queries) | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
WEEKDAY() | New Users to Excel | |||
Weekday | Excel Worksheet Functions | |||
Weekday | Excel Worksheet Functions |