Count how many times a name appears in a month

[dlafko1]

I am using an 2 excel spread sheets. One called monthly Yearly Calls and the other called Man Hour - Response Area Data 2023 this table is the main set of data.

In Man Hour - Response Area Data 2023
Column D has the area that i want to count.. Column B as the date.

In Monthly Yearly Calls
Column A has the name Column c is a month column.

I want to get from In Man Hour - Response Area Data 2023 how many times the name Auburn (lets say) appeared in January. I have an equation that does this for the year and references column A vs the actual name in case that changes over time.. Now i would like to know how to get it to count just a month.

IDK if we need a query or what. I'm no expert in a excel so please when providing an answer walk me thru it.

 

[etaf]

it can be improved if you want different months - by using a cell with the date in
so instead of 1/1/24 - you could enter that in say A1
and instead of 31/1/24 - then use
=EOMONTH(A1,0)

=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

Book7
	A	B	C
1	1/1/24	Year	Month
2	Total
3	Auburn		3
4	Deer Lake		0
5	East Brunswick		2
6	East Norwegian		0
7	Friedensburg		0
8	Hamburg		0
9	New Ringgold		0
10	North Manheim		6
11	Norwegian		0
12	Orwigsburg		3
13	Port Clinton		0
14	Pottsville		1
15	Shoemakersville		0
16	South Manheim		0
17	Walker Township		0
18	West Brunswick		0
19	Windsor		0
Sheet2

Cell Formulas
Range	Formula
C3:C19	C3	=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

 

[dlafko1]

i had to change some values to get it to work with the data
have you copied the formula down

Yep I got it sorry it was the date was day then month as soon as i changed it to month then day Works great now Thanks

 

[etaf]

you are welcome,
Yep, i'm in UK and so DD/MMM/YY is default format

 

[dlafko1]

it can be improved if you want different months - by using a cell with the date in
so instead of 1/1/24 - you could enter that in say A1
and instead of 31/1/24 - then use
=EOMONTH(A1,0)

=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

Book7
	A	B	C
1	1/1/24	Year	Month
2	Total
3	Auburn		3
4	Deer Lake		0
5	East Brunswick		2
6	East Norwegian		0
7	Friedensburg		0
8	Hamburg		0
9	New Ringgold		0
10	North Manheim		6
11	Norwegian		0
12	Orwigsburg		3
13	Port Clinton		0
14	Pottsville		1
15	Shoemakersville		0
16	South Manheim		0
17	Walker Township		0
18	West Brunswick		0
19	Windsor		0
Sheet2

Cell Formulas
Range	Formula
C3:C19	C3	=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

OK I get it that works out much better

Thanks

 

[dlafko1]

Sorry did not relize there was a page two to this post LOL

 

[etaf]

no problem
not another column
Just a cell A1

but could add a column for each month and put the month in row 1 of that column and use that

Or maybe even use something like today() , so pulls previous month ... But on the 1st of the next month - that will just change the data

anyway - different ways to do this - rather than have it in the actual formula and have to edit / replace them all

 

[dlafko1]

no problem
not another column
Just a cell A1

but could add a column for each month and put the month in row 1 of that column and use that

Or maybe even use something like today() , so pulls previous month ... But on the 1st of the next month - that will just change the data

anyway - different ways to do this - rather than have it in the actual formula and have to edit / replace them all

Love it thanks I am gonna go with the first solution I think that will be easiest for the person using it who is not very tech savy. Thank you.

 

[dlafko1]

@etaf

One more question with the main table Man hours there is a colum for Total Man hours Column I

How can i do a sum of those total hours for month and year? Would you use the same formulas? I dont think you could because your not counting them but summing them

Man Hour - Response Area Data 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Call Number	Date	Call	Area	Area ID	Number of People	Minutes	Total Minutes	Total Man Hours	Day of the Week	Time of Day	Shift	Officer	Notes
2	1	1/1/2024	AFA	West Brunswick	35	12	44	528	8.80	Monday	14:06	1	Y
3	2	1/3/2024	AFA	West Brunswick	35	10	30	300	5.00	Wednesday	13:20	1	Y
4	3	1/4/2024	MVA w/ Entrapment - CX enroute	West Brunswick	35	12	7	84	1.40	Thursday	14:20	1	Y
5	4	1/6/2024	Grain Dryer Fire	West Brunswick	35	8	144	1152	19.20	Saturday	6:51	3	Y
6	5	1/6/2024	MVA w/ No Injuries	Orwigsburg	56	12	33	396	6.60	Saturday	14:17	1	Y
7	6	1/7/2024	EMS Assist - Cardiac Arrest	West Brunswick	35	12	41	492	8.20	Sunday	12:54	1	Y
8	7	1/8/2024	MVA w/ Entrapment - CX enroute	East Brunswick	7	8	6	48	0.80	Monday	15:59	2	Y
9	8	1/9/2024	Pump Detail	North Manheim	18	8	70	560	9.33	Tuesday	21:41	2	Y
10	9	1/9/2024	Pump Detail	North Manheim	18	7	70	490	8.17	Tuesday	23:10	2	Y
11	10	1/13/2024	Tree Down	North Manheim	18	5	41	205	3.42	Saturday	7:54	1	Y
12	11	1/13/2024	MVA w/ Injuries	North Manheim	18	8	246	1968	32.80	Saturday	9:51	1	Y
13	12	1/13/2024	Tree Down	North Manheim	18	2	10	20	0.33	Saturday	13:03	1	Y
14	13	1/14/2024	AFA	North Manheim	18	6	15	90	1.50	Sunday	13:05	1	Y
15	14	1/14/2024	Tree Down	North Manheim	18	5	10	50	0.83	Sunday	13:10	1	Y
16	15	1/14/2024	Wire Down	North Manheim	18	7	69	483	8.05	Sunday	13:20	1	Y
17	16	1/14/2024	Wire Down	West Brunswick	35	6	4	24	0.40	Sunday	14:16	1	Y
18	17	1/20/2024	Apartment Building Fire - CX enroute	Pottsville	68	9	16	144	2.40	Saturday	13:10	1	Y
19	18	1/20/2024	MVA w/ No Injuries	Orwigsburg	56	17	52	884	14.73	Saturday	21:36	2	Y
20	19	1/21/2024	Steam Event - No Fire	West Brunswick	35	13	16	208	3.47	Sunday	20:32	2	Y
21	20	1/24/2024	House Fire - CX enroute	West Brunswick	35	7	15	105	1.75	Wednesday	8:10	1	Y
22	21	1/25/2024	AFA	East Brunswick	7	6	8	48	0.80	Thursday	19:08	2	Y
23	22	1/28/2024	Utilities Investigation	Orwigsburg	56	6	17	102	1.70	Sunday	23:48	3	Y
24	23	1/30/2024	Oil Burner Malfunction	Landingville	47	9	36	324	5.40	Tuesday	15:17	2	Y
main

Man Hour - Response Area Data 2024.xlsx
	A	B	C
1	1/1/2024	Year	Month
2	Training Hours	200	137
3	Admin Hours	80	50.5
4	Response Hours	190	145.08
Hours

Thanks

 

[etaf]

you are welcome

 

[dlafko1]

you are welcome

I'm taking it you did not see my other question About do a sum from one sheet to another same principle year and month