formula

[mon732]

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

 

[T. Valko]

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?

 

[mon732]

The dates are in worksheet Month A30:A41. The range to sum is in worksheet
claim detail U7:U97.

 

[T. Valko]

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?

 

[mon732]

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.

 

[Shane Devenshire]

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.

 

[mon732]

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.

 

[T. Valko]

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.