| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula
=sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct
formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
Do you have a column of dates?
You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
The dates are in worksheet Month A30:A41. The range to sum is in worksheet
claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
Well, that's a problem!
The ranges have to be the same size. Maybe you misunderstood me or, maybe I misunderstood you. Here's my understanding of what you're wanting to do: 11/27/2009...10 7/22/2209.....12 1/17/2009.....22 6/1/2009.......17 12/31/2009...20 You want to sum the numbers in the column on the right if the date in the column on the left is in a certain month. Is that what you want to do? -- Biff Microsoft Excel MVP "mon732" wrote in message ... The dates are in worksheet Month A30:A41. The range to sum is in worksheet claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
ok, so we would use m7:m97 which is the date reported in the claim detail
worksheet and u7:97 total incurred. To return the total of each incurred amount in column U for each month in column M into the Month worksheet. "T. Valko" wrote: Well, that's a problem! The ranges have to be the same size. Maybe you misunderstood me or, maybe I misunderstood you. Here's my understanding of what you're wanting to do: 11/27/2009...10 7/22/2209.....12 1/17/2009.....22 6/1/2009.......17 12/31/2009...20 You want to sum the numbers in the column on the right if the date in the column on the left is in a certain month. Is that what you want to do? -- Biff Microsoft Excel MVP "mon732" wrote in message ... The dates are in worksheet Month A30:A41. The range to sum is in worksheet claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
Hi,
Try =SUMIF(M7:M97,"Jan",U7:U97) This works if the months in column M are just entered and Jan, Feb,... If the dates are legal Excel dates: =SUMPRODUCT(--(MONTH(M7:M97)=MONTH(A30)),U7:U97) This will work if M7:M97 and A30 are all legal dates. The entry in A30 can be any date in the desired month. To copy it make the cell references to M and U absolute. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mon732" wrote: ok, so we would use m7:m97 which is the date reported in the claim detail worksheet and u7:97 total incurred. To return the total of each incurred amount in column U for each month in column M into the Month worksheet. "T. Valko" wrote: Well, that's a problem! The ranges have to be the same size. Maybe you misunderstood me or, maybe I misunderstood you. Here's my understanding of what you're wanting to do: 11/27/2009...10 7/22/2209.....12 1/17/2009.....22 6/1/2009.......17 12/31/2009...20 You want to sum the numbers in the column on the right if the date in the column on the left is in a certain month. Is that what you want to do? -- Biff Microsoft Excel MVP "mon732" wrote in message ... The dates are in worksheet Month A30:A41. The range to sum is in worksheet claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
I referenced you the wrong column. Column j7:j97 is the loss date entered as
7/26/07, column K is a month column entered as =TEXT(J7,"mmmm"). I don't know what the legal excel date is. "Shane Devenshire" wrote: Hi, Try =SUMIF(M7:M97,"Jan",U7:U97) This works if the months in column M are just entered and Jan, Feb,... If the dates are legal Excel dates: =SUMPRODUCT(--(MONTH(M7:M97)=MONTH(A30)),U7:U97) This will work if M7:M97 and A30 are all legal dates. The entry in A30 can be any date in the desired month. To copy it make the cell references to M and U absolute. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mon732" wrote: ok, so we would use m7:m97 which is the date reported in the claim detail worksheet and u7:97 total incurred. To return the total of each incurred amount in column U for each month in column M into the Month worksheet. "T. Valko" wrote: Well, that's a problem! The ranges have to be the same size. Maybe you misunderstood me or, maybe I misunderstood you. Here's my understanding of what you're wanting to do: 11/27/2009...10 7/22/2209.....12 1/17/2009.....22 6/1/2009.......17 12/31/2009...20 You want to sum the numbers in the column on the right if the date in the column on the left is in a certain month. Is that what you want to do? -- Biff Microsoft Excel MVP "mon732" wrote in message ... The dates are in worksheet Month A30:A41. The range to sum is in worksheet claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula
The dates are in worksheet Month A30:A41.
Ok, try this... I'm assuming A30:A41 = month names as TEXT entries: A30 = January A31 = February A32 = March ... A41 = December Enter this formula in the Month sheet in cell B30: =SUMIF('Claim Detail'!K$7:K$97,A30,'Claim Detail'!U$7:U$97) Copy down to B41. -- Biff Microsoft Excel MVP "mon732" wrote in message ... I referenced you the wrong column. Column j7:j97 is the loss date entered as 7/26/07, column K is a month column entered as =TEXT(J7,"mmmm"). I don't know what the legal excel date is. "Shane Devenshire" wrote: Hi, Try =SUMIF(M7:M97,"Jan",U7:U97) This works if the months in column M are just entered and Jan, Feb,... If the dates are legal Excel dates: =SUMPRODUCT(--(MONTH(M7:M97)=MONTH(A30)),U7:U97) This will work if M7:M97 and A30 are all legal dates. The entry in A30 can be any date in the desired month. To copy it make the cell references to M and U absolute. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "mon732" wrote: ok, so we would use m7:m97 which is the date reported in the claim detail worksheet and u7:97 total incurred. To return the total of each incurred amount in column U for each month in column M into the Month worksheet. "T. Valko" wrote: Well, that's a problem! The ranges have to be the same size. Maybe you misunderstood me or, maybe I misunderstood you. Here's my understanding of what you're wanting to do: 11/27/2009...10 7/22/2209.....12 1/17/2009.....22 6/1/2009.......17 12/31/2009...20 You want to sum the numbers in the column on the right if the date in the column on the left is in a certain month. Is that what you want to do? -- Biff Microsoft Excel MVP "mon732" wrote in message ... The dates are in worksheet Month A30:A41. The range to sum is in worksheet claim detail U7:U97. "T. Valko" wrote: Do you have a column of dates? You need to check the month of the date. Where is the range that contains your dates? Where is the range to sum? -- Biff Microsoft Excel MVP "mon732" wrote in message ... =sum('Claim Detail'!u$7:u$97,A30) - I can't figure out what the correct formula would be to get the sum of all claims incurred in column u7:u97 in the claim detail worksheet for each month of the year. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
