worksheet formulas

  • Thread starter Thread starter camp732
  • Start date Start date
C

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.
 
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.
 
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.
 
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)
 
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?
 
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.
 
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.
 
Help, I still can't get this to work.

Shane Devenshire said:
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
Click to expand...
 
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")
Click to expand...

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