count occurrences in various months

[clramjit]

HI,

I have a table I am working on and I would like to have a certain outcome, can you help?
The table has a Date column (A26:A35) with dates for the year, and a status column (I26:I35) with Open and Closed criteria. What I want to do is create another table to count the number of times "Open" and "Closed" occurred for each month on the date column.
the attached has an example. Please help!

Thanks,
CL

 

[AlanSidman]

In D4 =COUNTIFS($A$19:$A$24,">="&1/1/2018,$A$19:$A$24,"<="&1/31/2018,$B$19:$B$24,D3)

In E4=COUNTIFS($A$19:$A$24,">="&1/1/2018,$A$19:$A$24,"<="&1/31/2018,$B$19:$B$24,E3)

You will then have to create this formula changing the dates for each successive month.

 

[GraH - Guido]

Hi,
A pivot is the most simple solution.
Or alternative formula, very similar to Alans'.
=COUNTIFS($A$19:$A$24,">="&DATE(2018,ROW($A1),1),$A$19:$A$24,"<"&DATE(2018,ROW($A1)+1,1),$B$19:$B$24,D$3)

Both found in attached file.

EDIT: formula won't be very sustainable when you have data for more then 1 year.

 

[AlanSidman]

After looking at GraH - Guido response, I think a Pivot Table is the right solution. Less messy and quicker to do.

 

[clramjit]

Thank you guys, for the quick response. I'll look at both suggestions and decide. I am not too familiar with Pivot tables though. Guess it's a good time to start learning.

Rgds
CL