Count if date matches specific month

[Papi]

Rows are created when maintenance is performed on equipment. The date will be entered in column A of each row. Yes will be entered each time a filter is changed (Air Filter, Oil Filter and Hydraulic filter. The Main worksheet is where the data is entered. The Month worksheet is where the count of filters is to be calculated. I simply entered the number of filters to give an example of how the totals would look based on the main worksheet. March shows 4 dates of which 2 air filters were used, 2 Oil filters were used and 1 Hydraulic filter.
Thank you.

MAIN WORKSHEET

Date	Air	Oil	Hydr
Mon Jan 06	Yes	Yes	Yes
Wed Feb 05		Yes
Fri Feb 28		Yes
Fri Mar 06	Yes		Yes
Mon Mar 16		Yes
Mon Mar 23		Yes
Mon Mar 30	Yes
Tue Apr 07	Yes	Yes
Sat Apr 18	Yes	Yes
Thu Apr 30	Yes	Yes	Yes

MONTH COUNTS WORKSHEET

Month	Air Filter	Hydraulic	Oil
Jan	1​	1​	1​
Feb			2​
Mar	2​	1​	2​
Apr	3​	1​	3​
Total	6​	3​	8​

 

[smitpau]

Hi,

I'll have a look more at this a little later as this probably isn't the best way.

Firstly you'll need to make the date it actual dates formats so the date formulas will work.

You can add a column to the left with the month and year if necessary.

Then use a formula like below, with the C17 cell referencing the month so say Feb, you can add another criteria for year as well.

Air Formula
=COUNTIFS(C2:C11,"Yes",C2:C11,MONTH(C17))

Month	Date	Air	Oil	Hydr
2​	08/02/2019​	Yes	Yes	Yes
2​	02/02/2019​	Yes	Yes
2​	03/02/2019​		Yes
2​	04/02/2019​	Yes		Yes
2​	05/02/2019​		Yes
2​	06/02/2019​		Yes
2​	07/02/2019​	Yes
2​	06/02/2019​	Yes	Yes
2​	09/02/2019​	Yes	Yes
2​	10/02/2019​	Yes	Yes	Yes

 

[LloydFinancials]

I created a counts worksheet with the filter types in B1:D1 and the months in A2:A5, then entered the following formula in B2 and copied across and down with shift+control+enter:
=SUMPRODUCT(IF(ISNUMBER(FIND($A2,Main!$A$3:$A$12)),1,0)*(Main!B$3:B$12="Yes"))

 

[Papi]

Sorry for not returning to respond to both replies. Thanks so much for all the help. It works great!