Count Number of Times A Word Appears within a Month

[wangaa11]

I have an excel document that has 5 different tabs and all the tabs have the same information. In a new tab I have a chart with the months in the top row and have the 5 departments along the side. I want to pull in the number of times "Yes" has appeared from the departments tab based on the month in the new tab.

I would upload an example but I am never able to.

Say for example, tab 1 through 5 is the departments with 10 columns of data, the date (dd,mmm,yyyy) is in column F and "yes" is in column H and I now want to count all the yes's from tab 1 in tab 6.

Appreciate any assistance.

 

[Asbestos_Jen]

If your source data can accept a helper column for the month formatted as text (and is laid out like I imagined), this would work:

Book1
	F	G	H	I
1	DATE		STATE	MONTH
2	1-Jan-23		YES	January
3	18-Aug-23		NO	August
4	14-Mar-23		NO	March
5	7-Feb-23		YES	February
6	20-Oct-23		NO	October
7	11-Mar-23		NO	March
8	19-Mar-23		YES	March
9	30-Sep-23		YES	September
10	3-Mar-23		YES	March
11	10-Dec-23		YES	December
12	1-Jun-23		NO	June
13	16-Jan-23		NO	January
14	17-Feb-23		NO	February
15	6-Dec-23		NO	December
16	22-Dec-23		YES	December
Dept1

Cell Formulas
Range	Formula
I2:I16	I2	=TEXT(F2,"mmmm")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2		January	February	March	April	May	June	July	August	September	October	November	December
3	Dept1	1	1	2	0	0	0	0	0	1	0	0	2
4	Dept2	3	0	0	0	1	1	1	0	0	0	0	1
5	Dept3	1	1	0	1	0	0	2	0	0	1	0	1
6	Dept4	1	1	0	0	0	1	0	1	1	1	0	1
7	Dept5	1	0	0	1	2	0	1	0	0	1	0	1
Sheet6

Cell Formulas
Range	Formula
B3:M3	B3	=COUNTIFS(Dept1!$I$2:$I$16,B$2,Dept1!$H$2:$H$16,"Yes")
B4:M4	B4	=COUNTIFS(Dept2!$I$2:$I$16,B$2,Dept2!$H$2:$H$16,"Yes")
B5:M5	B5	=COUNTIFS(Dept3!$I$2:$I$16,B$2,Dept3!$H$2:$H$16,"Yes")
B6:M6	B6	=COUNTIFS(Dept4!$I$2:$I$16,B$2,Dept4!$H$2:$H$16,"Yes")
B7:M7	B7	=COUNTIFS(Dept5!$I$2:$I$16,B$2,Dept5!$H$2:$H$16,"Yes")