| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COPY SEVERAL FORMULAS FROM ONE WORKSHEET TO ANOTHER WORKSHEET | Excel Worksheet Functions | |||
| Carrying formulas forward from Worksheet to Worksheet | Excel Discussion (Misc queries) | |||
| Excel 2003 - Linking Formulas, Worksheet to Worksheet | Excel Discussion (Misc queries) | |||
| Linking Formulas, Worksheet to Worksheet - Excel 2003 | Excel Discussion (Misc queries) | |||
| Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Linear Mode
