Help with Stat tracking

[murphyryans]

Hello,
I am trying to work on summarizing some statistics from an excel spreadsheet my entire team is using. But 700 lines in, there are some inconsistencies. Hoping you can help me find a formula to build to make up for these inconsistencies.

What I am trying to track whether a job was completed and by which Line Center/PMO. When i created the initial formula =COUNTIFS(E:E, J8, B:B, "Yes") it depended on our team both checking the box that says yes or no on Column B and selecting the Line Center from the drop down. What happened was people were making slight mistakes in the line center names. Instead of selecting "Gresham" They were typing "Gresham " and when that inconsistency happened it no longer showed that as a stat for Gresham. Besides me going through every line to ensure they selected the correct name, are there any other methods to have excel help me out. In my mini sheet I have a few inconsistencies to see if you all can help.

Minisheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Yes	Worked Hot (Y/N)	Region	Line Center/PMO	Crew called	Created POSS				62%	Percentage crew called
2	6/1/2023	Yes	No	Eastern	Gresham	No
3	6/1/2023	Yes	No	Eastern	B&M PMO	No	No		Monthly Jobs Canceled/Rescheduled		5
4	6/1/2023	Yes	Yes	Eastern	B&M PMO	Yes	No		Percentage Canceled/Rescheduled		14%
5	6/1/2023	Yes	No	Eastern	DJs PMO	Yes	No
6	6/1/2023	Yes	No	Eastern	Gresham	No	No				"No"	Yes
7	6/1/2023	Yes	No	Eastern	DJs PMO	Yes	No		Contractors		3	15
8	6/1/2023	Yes	Yes	Eastern	DJs PMO	Yes			B&M PMO		3	7
9	6/1/2023	Yes	No	Eastern	DJs PMO	Yes	No		DJs PMO		0	8
10	6/1/2023	Yes	No	Eastern	PSC	No	No		Henkels & McCoy		0	0
11	6/1/2023	Yes	No	Eastern	DJs PMO	Yes	No		ILB PMO		0	0
12	6/1/2023	Yes	No	Eastern	DJs PMO	Yes	No		Potelco PMO		0	0
13	6/1/2023	Yes	Yes	Southern	Sunset	No			Wilson Const. PMO		0	0
14	6/1/2023	Yes	No	Southern	Newberg	Yes			Sturgeon PMO		0	0
15	6/1/2023	No	No	Southern	B&M PMO	No
16	6/1/2023	No	No	Southern	B&M PMO	No			Eastern		0	5
17	6/1/2023	No	No	Southern	B&M PMO	No			Gresham		0	3
18	6/1/2023	Yes	No	Southern	B&M PMO	No			OC		0	0
19	6/1/2023	Yes	No	Southern	B&M PMO	Yes			PSC		0	2
20	6/1/2023	Yes	No	Southern	Woodburn	Yes			Core		0	0
21	6/1/2023	Yes	Yes	Southern	Sunset	No
22	6/1/2023	Yes	No	Southern	Newberg	Yes			Western		2	4
23	6/1/2023	Yes	No	Southern	B&M PMO	No	No		Beaverton		0	1
24	6/1/2023	Yes	No	Southern	Newberg	Yes			Sunset		2	3
25	6/1/2023	Yes	No	Western	Wilsonville	Yes	No
26	6/1/2023	No	No	Western	Sunset	No
27	6/1/2023	No	No	Western	Sunset	Yes			Southern		0	2
28	6/1/2023	Yes	No	Western	Beaverton	Yes			Newberg		0	2
29	6/1/2023	Yes	No	Western	Sunset	No			Salem		0	0
30	6/1/2023	Yes	Yes	Western	B&M PMO	Yes			Sheridan		0	0
31	6/2/2023	Yes	No	Eastern	B&M PMO	Yes			Woodburn		0	0
32	6/2/2023	Yes	No	Eastern	Gresham	Yes	No
33	6/2/2023	Yes	No	Eastern	DJs PMO	Yes	No
34	6/2/2023	Yes	No	Eastern	PSC	Yes	No		Total		5	26	31
35	6/2/2023	Yes	No	Eastern	DJs PMO	Yes	No
June 2023

Cell Formulas
Range	Formula
K1	K1	=COUNTIF(F2:F2330, "Yes")/COUNTA(F2:F2330)
K3	K3	=COUNTIF(B:B, "No")
K4	K4	=COUNTIF(B:B, "No")/(COUNTIF(B:B, "Yes")+COUNTIF(B:B, "No"))
K7:L7	K7	=SUM(K8:K14)
K8:K14,K28:K31,K23:K24,K17:K20	K8	=COUNTIFS(E:E, I8, B:B, "No")
L8:L14,L28:L31,L23:L24,L17:L20	L8	=COUNTIFS(E:E, I8, B:B, "Yes")
K16:L16,K27:L27	K16	=SUM(K17:K20)
K22:L22	K22	=SUM(K23:K24)
K34:L34	K34	=SUM(K27,K22,K16,K7)
M34	M34	=SUM(L34,K34)

Cells with Data Validation
Cell	Allow	Criteria
B1:C35	List	Yes, No
E:E	List	Beaverton, Core, Gresham, Newberg, OC, PSC, Salem, Sheridan, Sunset, Wilsonville, Woodburn, B&M PMO, DJs PMO, ILB PMO, Henkels & McCoy, Potelco PMO, Sturgeon PMO, Wilson Const. PMO
F:G	List	Yes, No

 

[ExceLoki]

for the extra spaces you could change your countif criteria1 to ""&CELL&"*"
as shown below

Book1
	I	J	K	L
6			"No"	Yes
7	Contractors		3	15
8	B&M PMO		3	7
9	DJs PMO		0	8
10	Henkels & McCoy		0	0
11	ILB PMO		0	0
12	Potelco PMO		0	0
13	Wilson Const. PMO		0	0
14	Sturgeon PMO		0	0
15
16	Eastern		0	5
17	Gresham		0	3
18	OC		0	0
19	PSC		0	2
20	Core		0	0
21
22	Western		2	4
23	Beaverton		0	1
24	Sunset		2	3
25
26
27	Southern		0	4
28	Newberg		0	3
29	Salem		0	0
30	Sheridan		0	0
31	Woodburn		0	1
Sheet1

Cell Formulas
Range	Formula
K7:L7	K7	=SUM(K8:K14)
K8:K14,K28:K31,K23:K24,K17	K8	=COUNTIFS(E:E, ""&I8&"*",B:B, "No")
L8:L14,L28:L31,L23:L24,L17:L20	L8	=COUNTIFS(E:E, ""&I8&"*", B:B, "Yes")
K16:L16,K27:L27	K16	=SUM(K17:K20)
K18:K20	K18	=COUNTIFS(E:E, I18, B:B, "No")
K22:L22	K22	=SUM(K23:K24)

 

[mumps]

Rather than typing the drop down list values directly into the "Source" box in the Data Validation pop up window, enter the list values in an empty column on your sheet or on a different sheet. Select the range in column E where you want the drop down list to appear and then in the Data Validation pop up window, select "List" in the "Allow" box, click on the "Source" box and then select the range where you manually entered the list values in an empty column. You will see the range containing the values entered in the "Source" box. This should prevent anyone from manually entering an incorrect value instead of selecting from the drop down list.

 

[awoohaw]

Here is an example of what I think @mumps is asking you to try (also a photo of the data validation dialog).

Mr excel questions 55.xlsm
	A	B	C	D	E			H	I
1	Date	Yes	Worked Hot (Y/N)	Region	Line Center/PMO
2	6/1/2023	Yes	No	Eastern	Gresham
3	6/1/2023	Yes	No	Eastern	B&M PMO
4	6/1/2023	Yes	Yes	Eastern	B&M PMO
5	6/1/2023	Yes	No	Eastern	DJs PMO
6	6/1/2023	Yes	No	Eastern	Gresham
7	6/1/2023	Yes	No	Eastern	DJs PMO				Validation List:
8	6/1/2023	Yes	Yes	Eastern	DJs PMO				B&M PMO
9	6/1/2023	Yes	No	Eastern	DJs PMO				DJs PMO
10	6/1/2023	Yes	No	Eastern	PSC				Henkels & McCoy
11	6/1/2023	Yes	No	Eastern	DJs PMO				ILB PMO
12	6/1/2023	Yes	No	Eastern	DJs PMO				Potelco PMO
13	6/1/2023	Yes	Yes	Southern	Sunset				Wilson Const. PMO
14	6/1/2023	Yes	No	Southern	Newberg				Sturgeon PMO
15	6/1/2023	No	No	Southern	B&M PMO				Gresham
16	6/1/2023	No	No	Southern	B&M PMO				OC
17	6/1/2023	No	No	Southern	B&M PMO				PSC
18	6/1/2023	Yes	No	Southern	B&M PMO				Core
19	6/1/2023	Yes	No	Southern	B&M PMO				Beaverton
20	6/1/2023	Yes	No	Southern	Woodburn				Sunset
21	6/1/2023	Yes	Yes	Southern	Sunset				Newberg
22	6/1/2023	Yes	No	Southern	Newberg				Salem
23	6/1/2023	Yes	No	Southern	B&M PMO				Sheridan
24	6/1/2023	Yes	No	Southern	Newberg				Woodburn
25	6/1/2023	Yes	No	Western	Wilsonville				Wilsonville
26	6/1/2023	No	No	Western	Sunset
27	6/1/2023	No	No	Western	Sunset
28	6/1/2023	Yes	No	Western	Beaverton
29	6/1/2023	Yes	No	Western	Sunset
30	6/1/2023	Yes	Yes	Western	B&M PMO
31	6/2/2023	Yes	No	Eastern	B&M PMO
32	6/2/2023	Yes	No	Eastern	Gresham
33	6/2/2023	Yes	No	Eastern	DJs PMO
34	6/2/2023	Yes	No	Eastern	PSC
35	6/2/2023	Yes	No	Eastern	DJs PMO
Murphyryans

Cells with Data Validation
Cell	Allow	Criteria
E2:E35	List	=$I$8:$I$25

 

[murphyryans]

Thanks for input and help.
I am pretty close to having what I need. I cant actually post this spreadsheet because of sensitive information, but on the month I am currently working on after adding the data validation. My stats are showing

40 No's
533 Yes
14 left blank
587 Total

But, what the spreadsheet actually has
71 No's
566 Yes's
17 left blank
654 total

I also tried to do the data validation to the Yes/No Columns as well. Wondering if anyone has any other suggestions?

My Current Formula is =COUNTIFS(E2:E654, ""&N8&"*", B2:B654, "Yes")

 

[awoohaw]

you can make dummy data. Actually Mr. Excel encourages using dummied up data.
Also, I don't see your values in N8. And your original formula was using J8 as the search criteria, not N8. What has changed?
Why do you have a zero length string in front of N8 in the formula? Maybe you need/want an asterisk there as well?

 

[murphyryans]

Sorry to cause confusion. I created a semi dummy sheet in my upload. The name of each of my Line centers is N not J

I applied what I saw @ExceLoki recommend so that I could maybe help the any initial misspelling or additional spaces. Was that not right @awoohaw ?

 

[awoohaw]

Well, I can't see your line centers since you have not posted anything new.
You're asking the forum to recreate your scenario with a prose description and that can lead to misunderstanding and errors.
I do appreciate that you provided expected results. Still don't see a column N in anything you've provided.
As far as @ExceLoki 's suggested formula, yes you do emulate that. But you still apparently have errors (again without seeing what is in N). AND... I suggested an edit to it.
Please provide a new set of dummy data. (In my example I posted I hid all extraneous columns, which you can do too.)
And you only need 15-20 rows, as long as you have data that will "hit" your search criteria.