array formula

Can't figure out why this formula isn't working. Column A1:A30 contain
dates, starting with 9/1/05 and incrementing by one day. Column B1:B30
contain numbers. I want to look at column A, and if it's a Monday, then I
want to sum the values in column B.

={SUMPRODUCT((WEEKDAY(A1:A30,2)=1*($B$1:$B$30)))}

My only guess is that you can't use the WEEKDAY function as an array.
However, when I look at that part of the formula in the formula editor, it
looks like it is working.

=SUMPRODUCT(--(WEEKDAY(A1:A30,2)=1),B1:B30)

Originally Posted by Jonathan Cooper

Can't figure out why this formula isn't working. Column A1:A30 contain
dates, starting with 9/1/05 and incrementing by one day. Column B1:B30
contain numbers. I want to look at column A, and if it's a Monday, then I
want to sum the values in column B.

={SUMPRODUCT((WEEKDAY(A1:A30,2)=1*($B$1:$B$30)))}

My only guess is that you can't use the WEEKDAY function as an array.
However, when I look at that part of the formula in the formula editor, it
looks like it is working.

Jonathan,

I think you just mixed up your brackets. Try:
=SUMPRODUCT((WEEKDAY(A1:A30,2)=1)*($B$1:$B$30))

This seems to work for me. By the way, you don't need to enter SUMPRODUCT
as an array formula.

Another approach to this would be to use a helper column. Insert a new
column for column B. Set B1=WEEKDAY(A1) and copy down. Then you can use
=SUMIF(A1:A30,2,C1:C30)

Eric

"Jonathan Cooper" wrote:

> Can't figure out why this formula isn't working. Column A1:A30 contain
> dates, starting with 9/1/05 and incrementing by one day. Column B1:B30
> contain numbers. I want to look at column A, and if it's a Monday, then I
> want to sum the values in column B.
>
> ={SUMPRODUCT((WEEKDAY(A1:A30,2)=1*($B$1:$B$30)))}
>
> My only guess is that you can't use the WEEKDAY function as an array.
> However, when I look at that part of the formula in the formula editor, it
> looks like it is working.

Thanks. I knew is was something stupid, but it just wasn't jumping out at me.

"Eric" wrote:

> Jonathan,
>
> I think you just mixed up your brackets. Try:
> =SUMPRODUCT((WEEKDAY(A1:A30,2)=1)*($B$1:$B$30))
>
> This seems to work for me. By the way, you don't need to enter SUMPRODUCT
> as an array formula.
>
> Another approach to this would be to use a helper column. Insert a new
> column for column B. Set B1=WEEKDAY(A1) and copy down. Then you can use
> =SUMIF(A1:A30,2,C1:C30)
>
> Eric
>
> "Jonathan Cooper" wrote:
>
> > Can't figure out why this formula isn't working. Column A1:A30 contain
> > dates, starting with 9/1/05 and incrementing by one day. Column B1:B30
> > contain numbers. I want to look at column A, and if it's a Monday, then I
> > want to sum the values in column B.
> >
> > ={SUMPRODUCT((WEEKDAY(A1:A30,2)=1*($B$1:$B$30)))}
> >
> > My only guess is that you can't use the WEEKDAY function as an array.
> > However, when I look at that part of the formula in the formula editor, it
> > looks like it is working.

=SUMPRODUCT(--(WEEKDAY(A1:A30,1)=2),B1:B30)


worked for me;
HTH
"Morrigan" <Morrigan.1v1sef_1126195510.5207@excelforum-nospam.com> wrote in
message news:Morrigan.1v1sef_1126195510.5207@excelforum-nospam.com...
>
> =SUMPRODUCT(--(WEEKDAY(A1:A30,2)=1),B1:B30)
>
> Jonathan Cooper Wrote:
>> Can't figure out why this formula isn't working. Column A1:A30 contain
>> dates, starting with 9/1/05 and incrementing by one day. Column
>> B1:B30
>> contain numbers. I want to look at column A, and if it's a Monday,
>> then I
>> want to sum the values in column B.
>>
>> ={SUMPRODUCT((WEEKDAY(A1:A30,2)=1*($B$1:$B$30)))}
>>
>> My only guess is that you can't use the WEEKDAY function as an array.
>> However, when I look at that part of the formula in the formula editor,
>> it
>> looks like it is working.
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile:
> http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=465922
>