worksheet formulas
 

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

The weekdays are entered in the claim detail worksheet column L7 to L98 as a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98 as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in one
column
on the claim detail worksheet.

worksheet formulas
 

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98 as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in one
column
on the claim detail worksheet.

worksheet formulas
 

Its returning a value of 0. Is it because the L7:L98 column has a formula
already in it of =TEXT(J7,"dddd") returning the value of the day of the week?

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98 as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in one
column
on the claim detail worksheet.

worksheet formulas
 

In that case you would use the COUNTIF function. However, you could remove
column A completely and and us my formula to reference column J. And if you
wanted to see the weekday in the claim detail sheet you could just format
column J to dddd format in the Format Cells dialog box.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

The weekdays are entered in the claim detail worksheet column L7 to L98 as a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

L7:L98 column of the claim detail sheet looks like this containing a formula
=TEXT(J18,"dddd")
Thursday
Monday
Monday
Tuesday
Wednesday
Tuesday
Monday

Column J7:J98 is the Loss Date column

In the Weekday sheet I need it to count how many claims happened on a
Monday, Tuesday and so on. Should I just use the J7:J98 Loss Date column? I
can't see how to format column J to the "dddd" format either.


"Shane Devenshire" wrote:

In that case you would use the COUNTIF function. However, you could remove
column A completely and and us my formula to reference column J. And if you
wanted to see the weekday in the claim detail sheet you could just format
column J to dddd format in the Format Cells dialog box.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

The weekdays are entered in the claim detail worksheet column L7 to L98 as a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

Help, I still can't get this to work.

"Shane Devenshire" wrote:

In that case you would use the COUNTIF function. However, you could remove
column A completely and and us my formula to reference column J. And if you
wanted to see the weekday in the claim detail sheet you could just format
column J to dddd format in the Format Cells dialog box.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

The weekdays are entered in the claim detail worksheet column L7 to L98 as a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I need
to put a formula in weekday worksheet to reference in claim detail worksheet
how many claims occurred on a certain day of the week which is in one column
on the claim detail worksheet.

worksheet formulas
 

Its returning a value of 0.

Ok, then that means your listed weekdays don't match the result of the
formulas in L7:L98

=TEXT(J7,"dddd")

Let's assume the result of the formula is Monday and is in cell L7.

When you list the weekdays to be counted they have to be in the same format:

A2 = Monday
A3 = Tuesday
A4 = Wednesday
...
A8 = Sunday

=COUNTIF('Claim Detail'!L7,A2)

The result should be 1.


--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
Its returning a value of 0. Is it because the L7:L98 column has a
formula
already in it of =TEXT(J7,"dddd") returning the value of the day of the
week?

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98
as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then
try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday.
I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in
one
column
on the claim detail worksheet.

worksheet formulas
 

=COUNTIF('Claim Detail'!L$7:L$98,L111) This is the formula I put in
returning a 0. L107= Sunday, L108 Monday and so on....L111=Thursday In L7
there is a Thursday result, L8 Monday, L9 Monday, L10 Tuesday.

"T. Valko" wrote:

Its returning a value of 0.

Ok, then that means your listed weekdays don't match the result of the
formulas in L7:L98

=TEXT(J7,"dddd")

Let's assume the result of the formula is Monday and is in cell L7.

When you list the weekdays to be counted they have to be in the same format:

A2 = Monday
A3 = Tuesday
A4 = Wednesday
...
A8 = Sunday

=COUNTIF('Claim Detail'!L7,A2)

The result should be 1.


--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
Its returning a value of 0. Is it because the L7:L98 column has a
formula
already in it of =TEXT(J7,"dddd") returning the value of the day of the
week?

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98
as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then
try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday.
I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in
one
column
on the claim detail worksheet.

worksheet formulas
 

Ok, I am so dumb. I got it to work!!! I was putting the days of the week in
the claim detail sheet instead referencing the days in the weekday sheet.

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98 as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday. I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in one
column
on the claim detail worksheet.

worksheet formulas
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
Ok, I am so dumb. I got it to work!!! I was putting the days of the week
in
the claim detail sheet instead referencing the days in the weekday sheet.

"T. Valko" wrote:

Assume you have the weekdays listed in A2:A8.

Enter this formula in B2 and copy down to B8:

=COUNTIF('Claim Detail'!L$7:L$98,A2)

--
Biff
Microsoft Excel MVP


"camp732" wrote in message
...
The weekdays are entered in the claim detail worksheet column L7 to L98
as
a
formula =TEXT(J7,"dddd"). I need the weekday sheet to pull how many
claims
happened on Sunday, Monday and so on.

"Shane Devenshire" wrote:

suppose your weekdays are entered as text "Monday" and so on, then
try:

=SUMPRODUCT(--(TEXT(Sheet1!A$1:A$18,"dddd")=C1))

In this case Monday is in C1 and the range A1:A18 contains Excel legal
dates.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"camp732" wrote:

I have two worksheets one is claim detail and the other is weekday.
I
need
to put a formula in weekday worksheet to reference in claim detail
worksheet
how many claims occurred on a certain day of the week which is in
one
column
on the claim detail worksheet.