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,

I just got this one to work for me: =SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmm-yy")=TEXT($A3,"mmm-yy"))+0)

Now I just need it to include the other 2 sheets. Yes, at the moment there's only 3 sheets (howevere in the future there will be one more-new financial year).
Cheers, AImee

Right.

Since you have multiple sheets, one for each year fiscal (?) year, we will go back to your original lay-out which uses just month names in full extenso...

Monthly Claims Summary
Month	Claims Closed
July	6
Augustus	0
August	3
Augustus	0
September	3
Augustus	0
October	0
Augustus	0
November	0
Augustus	0

<TBODY>
</TBODY>

Create first a range with the relevant sheet names as shown below:

11-12

12-13

13-14

<TBODY>
</TBODY>

Select this range and name the selection as SheeetList.

B3, control+shift+enter, not just enter, and copy down:

=SUM(IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),
  ROW(R4:R200)-ROW(R4),0,1)),"mmmm")=A3,1))

 

[SarAExcel]

Hi Aladin,

I'm sorry but I'm really lost!
Do you want me to un-format column A??
I know how to name a range but I'm not sure if this is what you mean?
I can name each range appropriately (11-12; 12-12; 13-14) but then I'm not sure what you mean by:
"Select this range and name the selection as SheeetList".
Thanks in advance,
Aimee

 

[Aladin Akyurek]

Hi Aladin,

I'm sorry but I'm really lost!
Do you want me to un-format column A??

Yes. On the Summary sheet we return to your original lay-out.

I know how to name a range but I'm not sure if this is what you mean?
I can name each range appropriately (11-12; 12-12; 13-14) but then I'm not sure what you mean by:
"Select this range and name the selection as SheeetList".
Thanks in advance,
Aimee

Let's say that we have those sheet names in D2:D4. I want you name this range, i.e., D2:D4, as SheetList using the Name Box.

The following workbook implements the multiple sheets set up:
https://dl.dropboxusercontent.com/u/65698317/SarAExcel%20monthly%20claims%20summary.xlsx

 

[SarAExcel]

Thanks for getting back to me.

When I plug in the formula I get "0". I think because the formula is referring to column R on the summary page & not columns R on the other sheets??

I noticed in your spreadsheet that you got "1" for claims closed in July (B3). Shouldn't this be "6"?

Thanks in advance, Aimee

 

[Aladin Akyurek]

Thanks for getting back to me.

When I plug in the formula I get "0". I think because the formula is referring to column R on the summary page & not columns R on the other sheets??

I noticed in your spreadsheet that you got "1" for claims closed in July (B3). Shouldn't this be "6"?

Thanks in advance, Aimee

Hi Aimee,

I had the month names messed up. Also, resolved the Empty cells issue that might arise.

The formula is, extended to cover the empty cells issue:

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

=SUM(IF(ISNUMBER(1/N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),ROW(R4:R200)-ROW(R4),0,1))),IF(TEXT(N(OFFSET(INDIRECT("'"&TRANSPOSE(SheetList)&"'!R4:R200"),ROW(R4:R200)-ROW(R4),0,1)),"mmmm")=A3,1)))

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. When done properly, Excel itself puts a pair of { and } round the formula. (On Mac: Command+Return.)

So I refreshed the link that you already know:

https://dl.dropboxusercontent.com/u/65698317/SarAExcel%20monthly%20claims%20summary.xlsx

 

[SarAExcel]

Hi,

Thank you.
I tried the formula.
I’m trying to only count the claims closed in the year 2013-2014 (so July-December 2013 & January-June 2014). The formula is counting all claims closed in July in the 3 worksheets regardless of the year.
Cheers Aimee

 

[Aladin Akyurek]

Hi,

Thank you.
I tried the formula.
I’m trying to only count the claims closed in the year 2013-2014 (so July-December 2013 & January-June 2014). The formula is counting all claims closed in July in the 3 worksheets regardless of the year.
Cheers Aimee

My understanding is that you have sheets 11-12, 12-13, 13-14, etc. and you want to apply a count formula to all of them at once for each month. For example, if it must be a count regarding the month of July, it will count all claims closed in july of every sheet. If this is not right, please elaborate.

 

[SarAExcel]

Hi,

No sorry, it’s a monthly claims summary for claims closed for the financial year of 2013-2014. So I only want to count those claims that were closed during July-Dec 2013 & Jan-June 2014.
So for July, it should only count those claim that were closed in July 2013. For March, it should only count those claims closed in March 2014.
I can easily count them if I’m only referring to one worksheet but I just wasn’t sure how to do this with multiple sheets??
Thank you
Aimee

 

[Aladin Akyurek]

Hi,

No sorry, it’s a monthly claims summary for claims closed for the financial year of 2013-2014. So I only want to count those claims that were closed during July-Dec 2013 & Jan-June 2014.
So for July, it should only count those claim that were closed in July 2013. For March, it should only count those claims closed in March 2014.
I can easily count them if I’m only referring to one worksheet but I just wasn’t sure how to do this with multiple sheets??
Thank you
Aimee

March 2014 can only be found in your 13-14 sheet, and none of others, right?

 

[SarAExcel]

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