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]

no sorry i missed that
SUMIFS()
so the countifs
=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

then SUMIFS() , similar , but need to add the sum range first
so
=SUMIFS( Sheet1!$I$2:$I$100 , 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	D
1	1/1/24	Year	Month
2	Total
3	Auburn		3	28.86
4	Deer Lake		0	0
5	East Brunswick		2	1.6
6	East Norwegian		0	0
7	Friedensburg		0	0
8	Hamburg		0	0
9	New Ringgold		0	0
10	North Manheim		6	54.77
11	Norwegian		0	0
12	Orwigsburg		3	23.03
13	Port Clinton		0	0
14	Pottsville		1	2.4
15	Shoemakersville		0	0
16	South Manheim		0	0
17	Walker Township		0	0
18	West Brunswick		0	0
19	Windsor		0	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)
D3:D19	D3	=SUMIFS( Sheet1!$I$2:$I$100, Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

 

[dlafko1]

no sorry i missed that
SUMIFS()
so the countifs
=COUNTIFS(Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

then SUMIFS() , similar , but need to add the sum range first
so
=SUMIFS( Sheet1!$I$2:$I$100 , 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	D
1	1/1/24	Year	Month
2	Total
3	Auburn		3	28.86
4	Deer Lake		0	0
5	East Brunswick		2	1.6
6	East Norwegian		0	0
7	Friedensburg		0	0
8	Hamburg		0	0
9	New Ringgold		0	0
10	North Manheim		6	54.77
11	Norwegian		0	0
12	Orwigsburg		3	23.03
13	Port Clinton		0	0
14	Pottsville		1	2.4
15	Shoemakersville		0	0
16	South Manheim		0	0
17	Walker Township		0	0
18	West Brunswick		0	0
19	Windsor		0	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)
D3:D19	D3	=SUMIFS( Sheet1!$I$2:$I$100, Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0),Sheet1!$D$2:$D$100,A3)

Ok that yeilds a 0 result for me.. Here is the acutal sheet

Man Hour - Response Area Data 2024.xlsx
	A	B	C
1	1/1/2024	Year	Month
2	Training Hours	149.88	0
3	Admin Hours	141.08	50.5
4	Response Hours	136.08	145.08
Hours

Cell Formulas
Range	Formula
C2	C2	=SUMIFS( main!$I$2:$I$100, main!$B$2:$B$100,">="&$A$1,main!$B$2:$B$100,"<="&EOMONTH($A$1,0),main!$C$2:$C$100,A3)
B2:B4	B2	=SUM(main!I2:I250)

 

[etaf]

ok,
wheres training hours in the sheet
i assumed same criteria
OR do you just want MONTH and not the Area , and Column I happens to be training hours
if so just use

=SUMIFS( Sheet1!$I$2:$I$100 , Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0))
and it will sum column I for just January

 

[dlafko1]

ok,
wheres training hours in the sheet
i assumed same criteria
OR do you just want MONTH and not the Area , and Column I happens to be training hours
if so just use

=SUMIFS( Sheet1!$I$2:$I$100 , Sheet1!$B$2:$B$100,">="&$A$1,Sheet1!$B$2:$B$100,"<="&EOMONTH($A$1,0))
and it will sum column I for just January

Perfect that is what i needed so far training and admin hours are just manual inputs I am going to see if he wants to add them to his sheet. I really appreciate all this help.

In an an attempt to understand the
Count if, the location of the date range, twice and then the final one is the counting part you need What is the A at the very end for?

Sumif its the sum area first then the date ranges.. Right?

 

[etaf]

the count is just counting all the criteria that matches - so if they are all TRUE in a row - then that row is considered 1 and next is 2 etc
so not doing anything but counting the matches for a row

what A at the end ???
A3 in the count
that is matching the cell A3 , if it exists in the column D - so to match the area

 

[dlafko1]

the count is just counting all the criteria that matches - so if they are all TRUE in a row - then that row is considered 1 and next is 2 etc
so not doing anything but counting the matches for a row

what A at the end ???
A3 in the count
that is matching the cell A3 , if it exists in the column D - so to match the area

great thanks again

 

[dermie_72]

you would use sumifs instead of countifs
=sumifs(Sum Range, Criteria Range, Criteria, Criteria Range1, Criteria1...ect)