Excel count text in column match date only

**Hisseal** · 02-02-2011, 09:23 AM

What is the formula for counting how many CLOSED items on a specified date. Working in 2 work sheets.

I have created a define name for OPEN AND CLOSED in sheet 1 AND Sheet 2 is where I need to enter result in column C9, Column A9 has the Day to Day dates to match this formula.....not sure but........

I have tried this =COUNTIF(Open_Date,">=A9")-COUNTIF(Final_Status,">A9")

However this counts all of the CLOSED ITEMS not matching the date.

I am tracking month by month.

I have attached the worksheet.

The formula needs to be in sheet 2 called Open vs Closed
In the closed section C9.

Thanks in Advance

**TMS** · 02-02-2011, 10:06 AM

Maybe:

=SUMPRODUCT(--('To Do List data'!$F$5:$F$13>=$A9)*('To Do List data'!$K$5:$K$13="Closed"))

Regards

**Cutter** · 02-02-2011, 10:14 AM

I was going to say:

=SUMPRODUCT((Open_Date>=A9)*(Final_Status="Closed"))

BUT

You will have to correct one of your named ranges so that they both have the same ending row. As is, one named range is longer than the other.

I should point out that ALL of the dates in your sample DO match your criteria so ALL of the "Closed" values ARE being counted. If you change one of those dates to a lesser value you will see a reduction in the formula's return.

**TMS** · 02-02-2011, 10:15 AM

Ah, that's why that didn't work ... and I took the easy way out by using specific ranges.

**Hisseal** · 02-03-2011, 07:33 AM

Thanks for your reply,
I am trying to pull out only date(s) which match 5 January 2011 and count how many items was "Closed". Example there might of been on the 5th January 2011 five number of issues raised and 3 closed how to create this formula to capture the date vs closed out.

Thanks in advance....