Excel: Claims Closed per Month - Sumproduct

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

This question generated 49 answers. To proceed to the answers, click here.

This thread is current as of May 20, 2014.

For more resources for Microsoft Excel