[SOLVED] Count up each day of the week

[mmartens12]

I am keeping track of how many calls i get each day. In column A i have the
date i got the call. What i want to do is add up how many calls i get on
Monday, Tuesday etc.

This is the formula i have so far, but it gives me bogus data.

=SUMPRODUCT(--(WEEKDAY(Data!$A$5:$A$500,2)=1))

What am i doing wrong?

Thanks

[Chip Pearson]

Use the following array formula:
=SUMPRODUCT(IF(A1:A30="",0,--(WEEKDAY(A1:A30)=6)))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
enclose the formula in curly braces {}. Adjust the range A1:A30
to the range of your data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mmartens12" <u24614@uwe> wrote in message
news:63e4505820d6f@uwe...
>I am keeping track of how many calls i get each day. In column
>A i have the
> date i got the call. What i want to do is add up how many
> calls i get on
> Monday, Tuesday etc.
>
> This is the formula i have so far, but it gives me bogus data.
>
> =SUMPRODUCT(--(WEEKDAY(Data!$A$5:$A$500,2)=1))
>
> What am i doing wrong?
>
> Thanks
>

[Excelenator]

Your formula is giving you the total number of calls for ANY date that is a Monday. For example if you had 3 calls on Monday July 24th and 2 Calls on Monday July 17th your formula gives you the result 5 calls. In that sense I think it is working correctly according to the data that I tested it with.

[mmartens12]

Same formula as above but i get extra on Saturday?

7/3/06
7/4/06
7/5/06 All
7/6/06 Monday 2
7/7/06 Tuesday 2
7/8/06 Wednesday 2
7/9/06 Thursday 2
7/10/06 Friday 2
7/11/06 Saturday 8
7/12/06 Sunday 2
7/13/06
7/14/06 18
7/15/06
7/16/06

[mmartens12]

Thank you so much that worked!

When do i know it is an array formula, and press CTRL+SHIFT+ENTER?

[mmartens12]

Do i need to hit CTRL+SHIFT+ENTER when i enter in the formula?

Excelenator wrote:
>Your formula is giving you the total number of calls for ANY date that
>is a Monday. For example if you had 3 calls on Monday July 24th and 2
>Calls on Monday July 17th your formula gives you the result 5 calls.
>In that sense I think it is working correctly according to the data
>that I tested it with.
>

[Bob Phillips]

No you don't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mmartens12" <u24614@uwe> wrote in message news:63e4c4ca557af@uwe...
> Do i need to hit CTRL+SHIFT+ENTER when i enter in the formula?
>
> Excelenator wrote:
> >Your formula is giving you the total number of calls for ANY date that
> >is a Monday. For example if you had 3 calls on Monday July 24th and 2
> >Calls on Monday July 17th your formula gives you the result 5 calls.
> >In that sense I think it is working correctly according to the data
> >that I tested it with.
> >
>

[Bob Phillips]

I'm lost on this one Chip.

What's wrong with

=SUMPRODUCT(--(WEEKDAY(A1:A30)=6))

the only day it has a problem with is Sat if there are blanks, and that can
be avoided with

=SUMPRODUCT(--(A1:A30<>""),--(WEEKDAY(A1:A30)=7))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> Use the following array formula:
> =SUMPRODUCT(IF(A1:A30="",0,--(WEEKDAY(A1:A30)=6)))
>
> Since this is an array formula, you must press CTRL+SHIFT+ENTER
> rather than just Enter when you first enter the formula and
> whenever you edit it later. If you do this properly, Excel will
> enclose the formula in curly braces {}. Adjust the range A1:A30
> to the range of your data.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "mmartens12" <u24614@uwe> wrote in message
> news:63e4505820d6f@uwe...
> >I am keeping track of how many calls i get each day. In column
> >A i have the
> > date i got the call. What i want to do is add up how many
> > calls i get on
> > Monday, Tuesday etc.
> >
> > This is the formula i have so far, but it gives me bogus data.
> >
> > =SUMPRODUCT(--(WEEKDAY(Data!$A$5:$A$500,2)=1))
> >
> > What am i doing wrong?
> >
> > Thanks
> >
>
>

[Chip Pearson]

> When do i know it is an array formula, and press
> CTRL+SHIFT+ENTER?

See http://www.cpearson.com/excel/array.htm for an introduction
to array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"mmartens12" <u24614@uwe> wrote in message
news:63e4c135e5181@uwe...
> Thank you so much that worked!
>
> When do i know it is an array formula, and press
> CTRL+SHIFT+ENTER?
>

[Chip Pearson]

> I'm lost on this one Chip.

I just wasn't thinking. Your second formula (that accounts for
blanks) works better than mine. I've been writing VB6 code 10
hours a day for the last 6 months, and my formula skills are a
bit rusty.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> I'm lost on this one Chip.
>
> What's wrong with
>
> =SUMPRODUCT(--(WEEKDAY(A1:A30)=6))
>
> the only day it has a problem with is Sat if there are blanks,
> and that can
> be avoided with
>
> =SUMPRODUCT(--(A1:A30<>""),--(WEEKDAY(A1:A30)=7))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing
> direct)
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> Use the following array formula:
>> =SUMPRODUCT(IF(A1:A30="",0,--(WEEKDAY(A1:A30)=6)))
>>
>> Since this is an array formula, you must press
>> CTRL+SHIFT+ENTER
>> rather than just Enter when you first enter the formula and
>> whenever you edit it later. If you do this properly, Excel
>> will
>> enclose the formula in curly braces {}. Adjust the range
>> A1:A30
>> to the range of your data.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "mmartens12" <u24614@uwe> wrote in message
>> news:63e4505820d6f@uwe...
>> >I am keeping track of how many calls i get each day. In
>> >column
>> >A i have the
>> > date i got the call. What i want to do is add up how many
>> > calls i get on
>> > Monday, Tuesday etc.
>> >
>> > This is the formula i have so far, but it gives me bogus
>> > data.
>> >
>> > =SUMPRODUCT(--(WEEKDAY(Data!$A$5:$A$500,2)=1))
>> >
>> > What am i doing wrong?
>> >
>> > Thanks
>> >
>>
>>
>
>

[Bob Phillips]

Thanks for the info, I thought I was missing something.

Regards

Bob

"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
> > I'm lost on this one Chip.
>
> I just wasn't thinking. Your second formula (that accounts for
> blanks) works better than mine. I've been writing VB6 code 10
> hours a day for the last 6 months, and my formula skills are a
> bit rusty.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > I'm lost on this one Chip.
> >
> > What's wrong with
> >
> > =SUMPRODUCT(--(WEEKDAY(A1:A30)=6))
> >
> > the only day it has a problem with is Sat if there are blanks,
> > and that can
> > be avoided with
> >
> > =SUMPRODUCT(--(A1:A30<>""),--(WEEKDAY(A1:A30)=7))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing
> > direct)
> >
> > "Chip Pearson" <[email protected]> wrote in message
> > news:[email protected]...
> >> Use the following array formula:
> >> =SUMPRODUCT(IF(A1:A30="",0,--(WEEKDAY(A1:A30)=6)))
> >>
> >> Since this is an array formula, you must press
> >> CTRL+SHIFT+ENTER
> >> rather than just Enter when you first enter the formula and
> >> whenever you edit it later. If you do this properly, Excel
> >> will
> >> enclose the formula in curly braces {}. Adjust the range
> >> A1:A30
> >> to the range of your data.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "mmartens12" <u24614@uwe> wrote in message
> >> news:63e4505820d6f@uwe...
> >> >I am keeping track of how many calls i get each day. In
> >> >column
> >> >A i have the
> >> > date i got the call. What i want to do is add up how many
> >> > calls i get on
> >> > Monday, Tuesday etc.
> >> >
> >> > This is the formula i have so far, but it gives me bogus
> >> > data.
> >> >
> >> > =SUMPRODUCT(--(WEEKDAY(Data!$A$5:$A$500,2)=1))
> >> >
> >> > What am i doing wrong?
> >> >
> >> > Thanks
> >> >
> >>
> >>
> >
> >
>
>