formula

  • Thread starter Thread starter mon732
  • Start date Start date
M

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.
 
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?
 
The dates are in worksheet Month A30:A41. The range to sum is in worksheet
claim detail U7:U97.
 
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?
 
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.
 
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.
 
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.
 
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.
 
Back
Top