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

 

[SarAExcel]

Sorry columns are saving all over the place. Columns across the top should be spaced from A-M and rows down the side should be 1-17

 

[SarAExcel]

Hi there

I'm hoping you'll be able to assist me.
Here's a link to a screenshot of the spreadsheet I'm having trouble with.
1.JPG - Google Drive

As I mentioned in my previous message, 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 August).

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))


Would be great to have your thoughts!
Thanks in advance

 

[SarAExcel]

You are welcome.

Hi there

I'm hoping you'll be able to assist me.
Here's a link to a screenshot of the spreadsheet I'm having trouble with.
1.JPG - Google Drive

As I mentioned in my previous message, 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 August).

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))


Would be great to have your thoughts!
Thanks in advance

 

[Aladin Akyurek]

A couple of questions:

1. Do these formula differ from the one I suggested?

2. Care to provide the data in an Excel readable here directly here instead of a .jpg, which requires retyping?

 

[SarAExcel]

Hi there
So sorry to waste your time. I've just worked out the problem....2 dates from the other sheets had been entered incorrectly. It's now working perfectly. Thanks so much for getting back to me.
Have a great day
Cheers

 

[Aladin Akyurek]

Hi there
So sorry to waste your time. I've just worked out the problem....2 dates from the other sheets had been entered incorrectly. It's now working perfectly. Thanks so much for getting back to me.
Have a great day
Cheers

Great, relieved. Thanks for the update.

 

[oldbrewer]

claim

opened

closed

C1

01/06/2012

03/03/2013

C2

24/07/2012

18/04/2013

problem statement

C3

15/09/2012

03/06/2013

C4

07/11/2012

19/07/2013

count how many claims were closed

C5

30/12/2012

03/09/2013

between july 1 2015 and june 30 2016

C6

21/02/2013

C7

15/04/2013

04/12/2013

C8

07/06/2013

19/01/2014

C9

30/07/2013

06/03/2014

01/07/2015

C10

21/09/2013

30/06/2016

C11

13/11/2013

06/06/2014

C12

05/01/2014

22/07/2014

C13

27/02/2014

06/09/2014

5

=SUMPRODUCT(($C$2:$C$41>=$F$10)*($C$2:$C$41<=$F$11))

C14

21/04/2014

C15

13/06/2014

07/12/2014

C16

05/08/2014

22/01/2015

C17

27/09/2014

09/03/2015

if you put all your data in one sheet (maybe by automatic extraction from other sheets)

C18

19/11/2014

it becomes a very simple calculation

C19

11/01/2015

09/06/2015

C20

05/03/2015

25/07/2015

x

C21

27/04/2015

C22

19/06/2015

25/10/2015

x

C23

11/08/2015

10/12/2015

x

C24

03/10/2015

C25

25/11/2015

11/03/2016

x

C26

17/01/2016

26/04/2016

x

C27

10/03/2016

C28

02/05/2016

27/07/2016

C29

24/06/2016

C30

16/08/2016

27/10/2016

C31

08/10/2016

12/12/2016

C32

30/11/2016

C33

22/01/2017

14/03/2017

C34

16/03/2017

C35

08/05/2017

C36

30/06/2017

C37

22/08/2017

C38

14/10/2017

C39

06/12/2017

C40

28/01/2018

<colgroup><col><col span="2"><col span="2"><col><col span="10"></colgroup><tbody>
</tbody>