I have a spreadsheet used for reporting on issue stats. On a daily basis I take a csv from the issues DB and dump it into a tab within the workbook.

What I am trying to work out is how to best do a formula to count the number of issues no longer open (status of: Closed, Resolved or Rejected) that happened on a certain date (Sequential Dates column below). The system I am pulling information from has an Updated Date column which covers the last time the record was edited, which isn't necessarily the close date as issues still open are edited regularly.

These are the columns I am working with:
  • Data - column F
  • Status - column G
  • Reformatted date - column H
  • Sequential Dates - column I
  • Issues - column J


Data currently returns the updated date in the following format: 20/12/2013 15:14
Status is the status of the issue (Assigned, Feedback - Testing, Reopen, Roadmap, New, Closed, Resolved, Rejected) - I need to return those that have a Closed, Resolved and Rejected status.
Reformatted date is using =DAY(F3) & "/" & MONTH(F3) & "/" & YEAR(F3) to get a 20/12/2013 format.
Sequential date is the dates I'm reporting on

What I'm trying to get is the total for the issues column.

I have tried sumproduct, countif and countifs to no avail. I know I need to write a formula that counts how many times the sequential date (column I) shows up in the reformatted date (column H) column AND matches the criteria of "Closed", "Rejected" and "Resolved" from column G. If somebody could please help, that would be wonderful.

Thank you in advance.