Claims Closed per Month - Sumproduct

[SarAExcel]

Hi all,

I’m doing a monthly claims summary.

• Column A: Month (in order from July through to June)
• Column B: Number of claims closed

-The data for this financial year is on worksheet: “13-14”
-Column “R” contains the date the claim was closed (if it has been closed yet)

So to work out the number of claims closed for each month of 2013-2014, I’ve used the following formula:
=SUMPRODUCT(1*(MONTH('13-14'!R$4:R$200)=7))
(The last number changes depending on the month. The above example is for the month of July “7”)

The problem is, some claims from the previous financial years were only closed this year. So I need to include these as well.
The other worksheets/years that I need to include are labelled: “11-12” & “12-13”
I only want to count the claims closed from these years if the claims were closed this financial year (July 2013 – June 2014).
Can anyone suggest how I can do this??
Thanks in advance,
Aimee

 

[Michael M]

Maybe this, you'll need to change the ranges to suit

=SUMPRODUCT((MONTH('12-13'!R$1:R$30)=7)+(MONTH('13-14'!R$1:R$30)=7)+(MONTH('11-12'!R$1:R$30)=7))

 

[SarAExcel]

Hi Michael,

Thanks so much for your fast response!

The problem with that is it will also include the claims that were closed in the previous years. I only want to count the claims that were closed in the last financial year.
So for example, For worksheet 11-12, if the date in column R was “5/2/2012” then I would not want to include it. But if the date in column R was “11/5/2014” then I would want to include it.
I hope that makes sense?!
Thanks in advance,
Aimee

 

[Michael M]

Ok, you'll still have to change the ranges

=SUMPRODUCT((MONTH('12-13'!$R$1:$R$30)=7)*(YEAR('12-13'!$R$1:$R$30)=2014)+(MONTH('13-14'!$R$1:$R$30)=7)*(YEAR('13-14'!$R$1:$R$30)=2014)+(MONTH('11-12'!$R$1:$R$30)=7)*(YEAR('11-12'!$R$1:$R$30)=2014))

You could make it easier by making the formula reference a range value instead for the month and year...let's say cell A1 & B1

=SUMPRODUCT((MONTH('12-13'!$R$1:$R$30)=A1)*(YEAR('12-13'!$R$1:$R$30)=B1)+(MONTH('13-14'!$R$1:$R$30)=A1)*(YEAR('13-14'!$R$1:$R$30)=B1)+(MONTH('11-12'!$R$1:$R$30)=A1)*(YEAR('11-12'!$R$1:$R$30)=B1))

 

[SarAExcel]

Hi Michael,

Thanks again for your reply, I really appreciate it.

This is proving to be a tricky one!
Unfortunately that won’t work either as the last financial year is not just 2014, its July 2013 until June 2014.

Any ideas?
Thanks again,
Aimee

 

[Michael M]

So the months are not relevent for any non current years ??....

 

[SarAExcel]

Hi,
Maybe it’s too late in the afternoon for my brain, but I’m not too sure what you mean.
The months that matter are:

• July through to Dec for 2013 &
• Jan through to June for 2014

The basic layout will be:

Monthly Claims Summary 2013-2014
Month	Claims Closed
July
August
September
October
November
December
January
February
March
April
May
June

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Michael M]

What I'm saying is ...there is only one July, for instance, in the list regardless of the year. !

 

[SarAExcel]

Hi Michael,

Yes, thats right.
Cheers, Aimee

 

[Michael M]

Ok, in that case the formula should be fine.......unless I'm misunderstanding the original request....I'm pretty stupid sometimes...