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]

Hi Micahel,

Sorry for the delayed reply.

I see what you mean. So for the month of July I entered the following formula:

=SUMPRODUCT((MONTH('13-14'!R$4:R$250)=7)*(YEAR('13-14'!R$4:R$250)=2013)+(MONTH('12-13'!R$4:R$250)=7)*(YEAR('12-13'!R$4:R$250)=2013)+(MONTH('11-12'!R$4:R$250)=7)*(YEAR('11-12'!R$4:R$250)=2013))

But I'm getting an error value: #N/A

Any idea where I'm going wrong??
Thanks in advance, Aimee

 

[Michael M]

The formula works OK for me !!
HAve you checked the ranges specified for any N/A errors if there is one in column "R" of any sheet, you will get the error

 

[Aladin Akyurek]

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>

Modify slightly your output lay-out in column A as follows:

1-Jul-2013
1-Aug-2013
...
1-Jun-2014

Select A3:A14 and custom format the selection as mmmm.

Now invoke in B3 and copy down:

Either...

=COUNTIFS('13-14'!R$4:R$200,">="&$A3,'13-14'!R$4:R$200,"<="&EOMONTH($A3,0))

Or...

=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=$A3)+0)

 

[SarAExcel]

Hi Michael,

I still can't get it to work.
I've checked the data in the ranges that I'm using & there doesn't appear to be any errors. There would be a little green triangle in the cells if there was or error message, and I can't see this.
Are you entering the formula exactly as I have??:
=SUMPRODUCT((MONTH('13-14'!R$4:R$250)=7)*(YEAR('13-14'!R$4:R$250)=2013)+(MONTH('12-13'!R$4:R$250)=7)*(YEAR('12-13'!R$4:R$250)=2013)+(MONTH('11-12'!R$4:R$250)=7)*(YEAR('11-12'!R$4:R$250)=2013))

Cheers,AImee

 

[Michael M]

Aladins solution is FAR more elegant, AND simpler !!

 

[SarAExcel]

Hi Aladin,

Thanks so much for your help.

I changed column A as you suggested.
Then used:
=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=$A3)+0)

But......I keep getting "0" in B3.
I also need to include 2 other work sheets in B3:
-'13-14'!R$4:R$200
-'11-12'!R$4:R$200

-Columns "R" are formatted as date (not sure if this makes any difference)?

I really want to get to the bottom of this. Any help is much appreciated!
Aimee

 

[Aladin Akyurek]

Hi Aladin,

Thanks so much for your help.

I changed column A as you suggested.
Then used:
=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=$A3)+0)

But......I keep getting "0" in B3.

My bad. I forgat to adjust for the A3 bit...

=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=TEXT($A3,"mmmm")+0)

I also need to include 2 other work sheets in B3:
-'13-14'!R$4:R$200
-'11-12'!R$4:R$200

-Columns "R" are formatted as date (not sure if this makes any difference)?

I really want to get to the bottom of this. Any help is much appreciated!
Aimee

We have already 13-14!... And, what is your version of Excel?



What is your version of Excel

 

[SarAExcel]

I tried again & it requested I add another bracket to make:
=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=TEXT($A3,"mmmm")+0))
But I just get error message: #VALUE

Oops, I meant to say:
- '11-12'!R$4:R$200
- '12-13'!R$4:R$200

I'm using Excel 2007
Thanks again,Aimee

 

[Aladin Akyurek]

I tried again & it requested I add another bracket to make:
=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmmm")=TEXT($A3,"mmmm")+0))
But I just get error message: #VALUE

B3:

Either...

=SUMPRODUCT((TEXT('13-14'!R$4:R$200,"mmm-yy")=TEXT($A3,"mmm-yy"))+0)

Or...

=COUNTIFS('13-14'!$R$4:$R$40,">="&$A3,'13-14'!$R$4:$R$40,"<="&EOMONTH($A3,0))

The latter is faster. However, having more than one data sheet complicate things...

Oops, I meant to say:
- '11-12'!R$4:R$200
- '12-13'!R$4:R$200

I'm using Excel 2007
Thanks again,Aimee

Do we have just 3 sheets to summarize?

 

[SarAExcel]

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