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

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

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

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.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565828

Thank you so much that worked!

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

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.

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.

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" wrote in message
...
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

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?

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" wrote in message
...
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" wrote in message
...
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

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

Regards

Bob

"Chip Pearson" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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