worksheet formulas

[camp732]

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.

 

[Shane Devenshire]

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.

 

[camp732]

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.

 

[T. Valko]

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)

 

[camp732]

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)

 

[camp732]

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?

 

[Shane Devenshire]

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.

 

[camp732]

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.

 

[camp732]

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

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

 

[T. Valko]

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.

 

[camp732]

=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.

 

[camp732]

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]

Good deal. Thanks for the feedback!