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

 

[Aladin Akyurek]

Hi Aladin,
No it can be found in all worksheets. A claim might be opened in 2012 but remain open until 2014 for example.

In that case, we will have the summary lay-out I have proposed initially:

Monthly Claims Summary 2013-2014
Month	Claims Closed
1-Jul-13	2
1-Aug-13	1
1-Sep-13	2
1-Oct-13	0
1-Nov-13	0
1-Dec-13	0
1-Jan-14	1
1-Feb-14	1
1-Mar-14	1
1-Apr-14	0

<TBODY>
</TBODY>

B3, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),
  ROW($R$4:$R$200)-ROW($R$4),0,1))),
  IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),
  ROW($R$4:$R$200)-ROW($R$4),0,1)),"m/yy")=TEXT(A3,"m/yy"),1)))

See:
https://dl.dropboxusercontent.com/u/65698317/SarAExcel monthly claims summary version-2.xlsx

 

[SarAExcel]

So far so good! It seems to be working=brilliant. Thank you very much!!

 

[Aladin Akyurek]

So far so good! It seems to be working=brilliant. Thank you very much!!

You're welcome. Thanks for providing feedback.

 

[SarAExcel]

Hi Aladin,
I’m wondering if you can assist me again?
It’s the new financial year, so I need to add another column to show claims closed in 2014-2015 (1/7/2014 – 30/6/2014).
I'm unsure of how to adjust the formula to suit the additional column (and keep the existing column for 2013-2014 unchanged).
Thanks in advance,
Aimee

 

[Aladin Akyurek]

Hi Aladin,
I’m wondering if you can assist me again?
It’s the new financial year, so I need to add another column to show claims closed in 2014-2015 (1/7/2014 – 30/6/2014).
I'm unsure of how to adjust the formula to suit the additional column (and keep the existing column for 2013-2014 unchanged).
Thanks in advance,
Aimee

You mean you have a new sheet for 2014-2015, that is, '14-15, no?

 

[SarAExcel]

Yes that's right

 

[Aladin Akyurek]

Yes that's right

Extend the SheetList range with '14-15 as in:

11-12

12-13

13-14

14-15

<TBODY>
</TBODY>

1. Activate Formulas | Name Manager
2. Select SheetList
3. Click on the Edit tab
4. Adjust the reference in the Refers to box
5. Click OK.

 

[SarAExcel]

Hi,
Thanks for your help!!
I've amended the sheet list.
What do I do now? I still need to display the claims closed in 2013-2014. This will just be an additional column (in this case it will be Column E).
Like this:

Year	2013-2014	2014-2015
Month	Claims Opened	Claims Closed	Claims Opened	Claims Closed
July	13	12	1	0
August	13	18	0	0
September	8	9	0	0
October	11	16	0	0
November	32	20	0	0
December	12	18	0	0
January	15	27	0	0
February	18	11	0	0
March	17	7	0	0
April	13	20	0	0
May	18	21	0	0
June	10	17	0	0

<tbody>
</tbody>

 

[Aladin Akyurek]

I lost the track of which representation you are using... The above exhibit has it as:

July

not as:

1-Jul-13.

Is it possible for you to upload the workbook to somewhere for download?

 

[SarAExcel]

Hi,
Oh yeah I see what you mean.
I can't upload it sorry (I'm not very good with technology lol).

So to get claims closed (Column E) for financial year 2014-2015, I would propbably need the formula to be linked to a new month column (instead of column A) so that it refers to year 14-15 & not 13-14.
Perhaps I could insert a month column somewhere & then hide it (just so the formula can work). Do you think this would work or is there a better solution?

Sorry I hope what I've said above makes some kind of sense to you.
Thanks in advance.