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]

What version of excel do you have , it would help to have that in profile as later versions have a lot more available functions and these may work better on closed spreadsheets


countifs()
are these 2 separate workbooks or sheets in the same worksheet

countifs( manhourrespnse!column B, ">="&1/1/23 , manhourrespnse!column B, "<="&31/1/23 , ColumnD , Auburn)

what are you using for Year?



Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[dlafko1]

I have office 365 Version 3212 64 bit

The are currently 2 separate workbooks. They could Easly be placed into one work book as separate sheets

Here is the link to the google drive I was not able to get the XL22bB to work

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

There is a 2023 version and a 2024 version

In Man Hour - Response Area Dat

I will keep working on the XL22dB

Thanks

 

[dlafko1]

This is the sheet i want the data to end in

Monthly Yearly Calls.xlsx
	A	B	C
1		Year	Month
2	Total	0
3	Auburn	2
4	Deer Lake	2
5	East Brunswick	22
6	East Norwegian	1
7	Friedensburg	1
8	Hamburg	2
9	New Ringgold	3
10	North Manheim	43
11	Norwegian	1
12	Orwigsburg	65
13	Port Clinton	1
14	Pottsville	7
15	Shoemakersville	2
16	South Manheim	1
17	Walker Township	1
18	West Brunswick	74
19	Windsor	1	1
Sheet1

Cell Formulas
Range	Formula
B2:B19	B2	=COUNTIF('H:\.shortcut-targets-by-id\1pGFKJ46Oxyu5_GN0Prj7aGWYkPtilVlz\Orwigsburg Fire Company Google Drive\Chief\MR. Excel Forum\[Man Hour - Response Area Data 2023.xlsx]Sheet1'!$D$2:$D$230,A2)
C19	C19	=COUNTIF('H:\.shortcut-targets-by-id\1pGFKJ46Oxyu5_GN0Prj7aGWYkPtilVlz\Orwigsburg Fire Company Google Drive\Chief\MR. Excel Forum\[Man Hour - Response Area Data 2023.xlsx]Sheet1'!$D$2:$D$230,A19)

This is the sheet the data would pull from

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-Jan	AFA	West Brunswick	35	12	44	528	8.80	Monday	14:06	1	Y
3	2	3-Jan	AFA	West Brunswick	35	10	30	300	5.00	Wednesday	13:20	1	Y
4	3	4-Jan	MVA w/ Entrapment - CX enroute	West Brunswick	35	12	7	84	1.40	Thursday	14:20	1	Y
5	4	6-Jan	Grain Dryer Fire	West Brunswick	35	8	144	1152	19.20	Saturday	6:51	3	Y
6	5	6-Jan	MVA w/ No Injuries	Orwigsburg	56	12	33	396	6.60	Saturday	14:17	1	Y
7	6	7-Jan	EMS Assist - Cardiac Arrest	West Brunswick	35	12	41	492	8.20	Sunday	12:54	1	Y
8	7	8-Jan	MVA w/ Entrapment - CX enroute	East Brunswick	7	8	6	48	0.80	Monday	15:59	2	Y
9	8	9-Jan	Pump Detail	North Manheim	18	8	70	560	9.33	Tuesday	21:41	2	Y
10	9	9-Jan	Pump Detail	North Manheim	18	7	70	490	8.17	Tuesday	23:10	2	Y
11	10	13-Jan	Tree Down	North Manheim	18	5	41	205	3.42	Saturday	7:54	1	Y
12	11	13-Jan	MVA w/ Injuries	North Manheim	18	8	246	1968	32.80	Saturday	9:51	1	Y
13	12	13-Jan	Tree Down	North Manheim	18	2	10	20	0.33	Saturday	13:03	1	Y
14	13	14-Jan	AFA	North Manheim	18	6	15	90	1.50	Sunday	13:05	1	Y
15	14	14-Jan	Tree Down	North Manheim	18	5	10	50	0.83	Sunday	13:10	1	Y
16	15	14-Jan	Wire Down	North Manheim	18	7	69	483	8.05	Sunday	13:20	1	Y
17	16	14-Jan	Wire Down	West Brunswick	35	6	4	24	0.40	Sunday	14:16	1	Y
18	17	20-Jan	Apartment Building Fire - CX enroute	Pottsville	68	9	16	144	2.40	Saturday	13:10	1	Y
19	18	20-Jan	MVA w/ No Injuries	Orwigsburg	56	17	52	884	14.73	Saturday	21:36	2	Y
20	19	21-Jan	Steam Event - No Fire	West Brunswick	35	13	16	208	3.47	Sunday	20:32	2	Y
21	20	24-Jan	House Fire - CX enroute	West Brunswick	35	7	15	105	1.75	Wednesday	8:10	1	Y
22	21	25-Jan	AFA	East Brunswick	7	6	8	48	0.80	Thursday	19:08	2	Y
23	22	28-Jan	Utilities Investigation	Orwigsburg	56	6	17	102	1.70	Sunday	23:48	3	Y
24	23	30-Jan	Oil Burner Malfunction	Landingville	47	9	36	324	5.40	Tuesday	15:17	2	Y
25	24	3-Feb	Landing Zone	North Manheim	18	8	36	288	4.80	Saturday	17:37	2	Y
Sheet1

 

[etaf]

you need to allow access to everyone on google drive to access the file

how about
=COUNTIFS(Sheet1!$B$2:$B$100,">="&DATEVALUE("1/1/2024"),Sheet1!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet1!$D$2:$D$100,A3)

BUT the dates can be put into another cell and used - so you can change the months

 

[dlafko1]

you need to allow access to everyone on google drive to access the file

how about
=COUNTIFS(Sheet1!$B$2:$B$100,">="&DATEVALUE("1/1/2024"),Sheet1!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet1!$D$2:$D$100,A3)

BUT the dates can be put into another cell and used - so you can change the months

Sorry I had it set to anyone with the link.. Also got the XL2BB working and attached those above. What do you mean change the months

This is the main data sheet

Man Hour - Response Area Data 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
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
25	24	2/3/2024	Landing Zone	North Manheim	18	8	36	288	4.80	Saturday	17:37	2	Y
Sheet1

This is the data sheet i am using to try and build some charts All in the same workbook now

Man Hour - Response Area Data 2024.xlsx
	A	B	C
1		Year	Month
2	Total	0	0
3	Auburn	0	0
4	Deer Lake	0	0
5	East Brunswick	2	0
6	East Norwegian	0	0
7	Friedensburg	0	0
8	Hamburg	0	0
9	New Ringgold	0	0
10	North Manheim	9	0
11	Norwegian	0	0
12	Orwigsburg	2	0
13	Port Clinton	0	0
14	Pottsville	1	0
15	Shoemakersville	0	0
16	South Manheim	0	0
17	Walker Township	0	0
18	West Brunswick	0	0
19	Windsor	0	#VALUE!
Sheet2

Cell Formulas
Range	Formula
B2:B19	B2	=COUNTIF(Sheet1!D1:D24,A2)
C2	C2	=COUNTIFS(Sheet2!$B$2:$B$100,">="&DATEVALUE("1/1/2024"),Sheet2!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet2!$D$2:$D$100,A3)
C3:C18	C3	=COUNTIFS(Sheet1!$D$2:$D$100,">="&DATEVALUE("1/1/2024"),Sheet1!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet1!$D$2:$D$100,A3)
C19	C19	=COUNTIF('H:\.shortcut-targets-by-id\1pGFKJ46Oxyu5_GN0Prj7aGWYkPtilVlz\Orwigsburg Fire Company Google Drive\Chief\MR. Excel Forum\[Man Hour - Response Area Data 2023.xlsx]Sheet1'!$D$2:$D$230,A19)

 

[etaf]

i used the xl2bb and replied earlier, with the formula

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

Cell Formulas
Range	Formula
B2:B19	B2	=COUNTIF('/Users/waynewalker/Downloads/H:\.shortcut-targets-by-id\1pGFKJ46Oxyu5_GN0Prj7aGWYkPtilVlz\Orwigsburg Fire Company Google Drive\Chief\MR. Excel Forum\[Man Hour - Response Area Data 2023.xlsx]Sheet1'!$D$2:$D$230,A2)
C3:C19	C3	=COUNTIFS(Sheet1!$B$2:$B$100,">="&DATEVALUE("1/1/2024"),Sheet1!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet1!$D$2:$D$100,A3)

 

[dlafko1]

i used the xl2bb and replied earlier, with the formula

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

Cell Formulas
Range	Formula
B2:B19	B2	=COUNTIF('/Users/waynewalker/Downloads/H:\.shortcut-targets-by-id\1pGFKJ46Oxyu5_GN0Prj7aGWYkPtilVlz\Orwigsburg Fire Company Google Drive\Chief\MR. Excel Forum\[Man Hour - Response Area Data 2023.xlsx]Sheet1'!$D$2:$D$230,A2)
C3:C19	C3	=COUNTIFS(Sheet1!$B$2:$B$100,">="&DATEVALUE("1/1/2024"),Sheet1!$B$2:$B$100,"<="&DATEVALUE("31/1/2024"),Sheet1!$D$2:$D$100,A3)

Ok sorry.. Just learning this.. So i pasted that into C3 and its not returning results

 

[dlafko1]

Ok sorry.. Just learning this.. So i pasted that into C3 and its not returning results

Never mind I got it the one date was backwards.. Thanks for the help

 

[etaf]

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