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,
Oh yeah I see what you mean.
I can't upload it sorry (I'm not very good with technology lol).

Ok. By the way, such uploads must not contains any sensitive data.

[quote[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 [/quote]

Yes, that's possible, but not very elegant and you have to repeat that for 15-16.

or is there a better solution?[...]

I guess the formula must be a bit more complex.

 

[SarAExcel]

Hi Aladin,

Thanks for your reply. Any suggestions on amending the formula to make it more elegant than my suggestion?
Thanks in advnace,
A

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your reply. Any suggestions on amending the formula to make it more elegant than my suggestion?
Thanks in advnace,
A

Monthly Claims Summary					SheetList
	13	14					11-12
	14	15					12-13
Month	Claims Closed					13-14
Jul	2	1					14-15
Aug	1	2
Sep	1	1
Oct	0	0
Nov	0	0
Dec	0	0
Jan	1	1
Feb	1	1
Mar	1	1
Apr	0	0
May	0	0
Jun	0	0

<TBODY>
</TBODY>

I propose to use 3-letter abbreviations of the month names on the summary sheet.

B5, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),
  ROW($R$4:$R$200)-ROW($R$4),0,1)),"mmmyy")=$A5&IF(ISNUMBER(MATCH($A5,
  {"Jul","Aug","Sep","Oct","Nov","Dec"},0)),B$2,B$3),1))

 

[SarAExcel]

Hello,
Sorry for the delayed response, I've been away for a few weeks.
Thanks so much for your response!!!
Is there a way to not have 13 & 14 on seperate rows? I just think it's going to look a bit strange/might be difficult for others to read.

My layout currently looks like this:

	A	B	C	D	E
1	Monthly Claims Summary
2	Year	2013-2014	2014-2015
3	Month	Claims Opened	Claims Closed	Claims Opened	Claims Closed
4	July	13	12	29
5	August	13	18	0
6	September	8	9	0
7	October	11	16	0
8	November	32	20	0
9	December	12	18	0
10	January	15	27	0
11	February	18	11	0
12	March	17	7	0
13	April	13	20	0
14	May	18	21	0
15	June	10	16	0

<tbody>
</tbody>

 

[Aladin Akyurek]

Hello,
Sorry for the delayed response, I've been away for a few weeks.
Thanks so much for your response!!!
Is there a way to not have 13 & 14 on seperate rows? I just think it's going to look a bit strange/might be difficult for others to read.
...

Post #43 presents a lay-out that is processable for the lay-out and the required processing correlates strongly in spreadsheet design.

That said, in order to introduce 2013-2014, 2014-2015, etc. we can do the following...

1) Create a sheet called Admin and move the range of SheetList to Admin.

2) Insert a row in front of the month and claims row.

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

<TBODY>
</TBODY>

The foregoing does not change the formulas of the required processing...

See the modified workbook:
https://dl.dropboxusercontent.com/u/65698317/SarAExcel monthly claims summary new lay-out.xlsx

 

[SarAExcel]

Hi,
Thanks again for your response.
I'm still having trouble making it work.
I'm happy to have the SheetList left on the same worksheet.
Is it possible to set out as per below, so:
-B2=2013
-C2=2014
-D2=2014
-E2=2015

	A	B	C	D	E
1	Monthly Claims Summary
2	Year	2013	2014	2014	2015
3	Month	Claims Opened	Claims Closed	Claims Opened	Claims Closed
4	July	13	12	29
5	August	13	18	0
6	September	8	9	0
7	October	11	16	0
8	November	32	20	0
9	December	12	18	0
10	January	15	27	0
11	February	18	11	0
12	March	17	7	0
13	April	13	20	0
14	May	18	21	0
15	June	10	16	0

<tbody>
</tbody>

 

[Aladin Akyurek]

Hi,
Thanks again for your response.
I'm still having trouble making it work.
...

I'd strongly advise to implement the set up of post #45 as described.

 

[SarAExcel]

Thank you,

I've set it ouit like yours & then just hidden rows 2 & 3 to improve the look.
Thanks very much for all your help.
Cheers!

 

[Aladin Akyurek]

Thank you,

I've set it ouit like yours & then just hidden rows 2 & 3 to improve the look.
Thanks very much for all your help.
Cheers!

You are welcome.

 

[SarAExcel]

You are welcome.

Hi!
I'm trying to update this spreadsheet to add the new financial year. Something is wrong though as it's showing 1 claim closed for both Oct (K9) and Dec (K11) (obviously incorrect as we are only in July). See copy of spreadsheet below.

The formula I have entered for cell K9 is:
=SUM(IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList2)&"'!R4:R500"),ROW($S$4:$S$500)-ROW($S$4),0,1)),"mmmyy")=$A9&IF(ISNUMBER(MATCH($A9,{"Jul","Aug","Sep","Oct","Nov","Dec"},0)),K$2,K$3),1))

The formula I have entered for cell K11 is:
=SUM(IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList2)&"'!R4:R500"),ROW($S$4:$S$500)-ROW($S$4),0,1)),"mmmyy")=$A11&IF(ISNUMBER(MATCH($A11,{"Jul","Aug","Sep","Oct","Nov","Dec"},0)),K$2,K$3),1))


A F G H I J K L M

1 MONTHLY CLAIMS SUMMARY
2		15		16		17
3		16		17		18
4 Year	2015-2016	2016-2017	2017-18
5 Month	Claims Opened	Claims Closed	Claims Opened	Claims Closed	Claims Opened	Claims Closed
6 Jul	19	22	19	17	0	0
7 Aug	23	15	16	12	0	0		Sheets
8 Sep	21	13	44	30	0	0		11-12
9 Oct	40	18	32	42	0	1		12-13
10 Nov	23	36	23	17	0	0		13-14
11 Dec	24	17	27	15	0	1		14-15
12 Jan	20	32	21	33	0	0		15-16
13 Feb	19	30	24	19	0	0		16-17
14 Mar	17	17	39	22	0	0		17-18
15 Apr	32	38	29	7	0	0
16 May	10	17	22	59	0	0
17 Jun	26	19	22	23	0	0

<tbody>
</tbody>

Hoping someone can help me work this out!!!
Thanks in advance
A