Sumif weekday

[Sungibungi]

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.

 

[Teethless mama]

=SUMPRODUCT(--(WEEKDAY(A2:A40)=1),B2:B40)

Adjust your range to suit, you can not use whole column pior to XL-2007

 

[Ron Rosenfeld]

=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

 

[Ron Rosenfeld]

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:A65535),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