adding a counta to a sumproduct

[Stephanie2021]

I am trying to count how many cells are not empty that matches up to the supervisor in the sample below

Training Score Card.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	NEW HIRE - DOES NOT NEED PRIOR MONTH TRAINING COMPLETED						JANUARY 2021 TRAINING SCORE CARD
2	Hire Date	Associate Name		Position	Team	Supervisor	Ammonia Awareness PPT	TRAINER INITIALS	Employee Ammonia Awareness Sign Off	TRAINER INITIALS	Serious Safety Policy
3	1/1/21	Jose	Doe	Worker	1	Jose	1/22/20	JA	1/22/20	JA	1/22/20
4	1/1/21	Joaquin	Doe	Worker	1	Brian	1/22/21	BE	1/22/21	BE	1/22/21
5	1/1/21	John	Doe	Worker	1	Hector	1/11/21	HS	1/11/21	HS	1/11/21
6	1/1/21	Joseph	Doe	Worker	1	Joe	1/19/21	JB	1/19/21	JB	1/19/21
7	1/1/21	Sally	Doe	Worker	1	Sally	1/27/21				1/27/21
8	1/1/21	Raymundo	Doe	Worker	1	Brian	1/22/21	BE	1/22/21	BE	1/22/21
9	1/1/21	Brian	Doe	Worker	1	Joe	1/19/21	JB	1/19/21	JB	1/19/21
10	1/1/21	Michael	Doe	Worker	1	Hector	1/11/21	HS	1/11/21	HS	1/11/21
11	1/1/21	Kapri	Doe	Worker	1	Chris	1/25/21	CG	1/25/21	CG	1/25/21
12	1/1/21	Gerardo	Doe	Worker	1	David	1/25/21	DI	1/25/21	DI	1/25/21
13	1/1/21	Brian	Doe	Worker	1	Brian	1/22/21	BE	1/22/21	BE	1/22/21
14	1/1/21	John	Doe	Worker	1	Hector	1/11/21	HS	1/11/21	HS	1/11/21
15	1/1/21	Wesley	Doe	Worker	1	Brian	1/22/21	BE	1/22/21	BE	1/22/21
16	1/1/21	Leopoldo	Doe	Worker	1	Hector	1/11/21	hs	1/11/21	hs	1/11/21
17	1/1/21	Santiago	Doe	Worker	1	David	1/25/21	DI	1/25/21	DI	1/25/21
18	1/1/21	Chris	Doe	Worker	1	Chris	1/25/21	CG	1/25/21	CG	1/25/21
19	1/1/21	Kyle	Doe	Worker	1	Joe	1/19/21	JB	1/19/21	JB	1/19/21
20	1/1/21	Peter	Doe	Worker	1	Brian	1/22/21	BE	1/22/21	BE	1/22/21
January

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J18	Cell	does not contain a blank value	text	NO
I18	Cell	does not contain a blank value	text	NO
K11	Cell	does not contain a blank value	text	NO
J11	Cell	does not contain a blank value	text	NO
I11	Cell	does not contain a blank value	text	NO
K18	Cell	does not contain a blank value	text	NO
K17:L17	Cell	does not contain a blank value	text	NO
I17:J17	Cell	does not contain a blank value	text	NO
G17:H17	Cell	does not contain a blank value	text	NO
K12:L12	Cell	does not contain a blank value	text	NO
I12:J12	Cell	does not contain a blank value	text	NO
K26,K16	Cell	does not contain a blank value	text	NO
I26,I16	Cell	does not contain a blank value	text	NO
J59:J60,J26,J16,J14,J10,J5	Cell	does not contain a blank value	text	NO
I14,K14	Cell	does not contain a blank value	text	NO
I10,K10	Cell	does not contain a blank value	text	NO
I5,K5	Cell	does not contain a blank value	text	NO
I3:J4,I25:J25,J61,I21,I8:J8,I13:J13,I15:J15,I20:J20,I27:J27,I36:J37,I39:J39,I45:J46,I48:J48,I53:J53,I56:J56	Cell	does not contain a blank value	text	NO
K3:L4,K25:L25,K21,K8:L8,K15:L15,K20:L20,K27:L27,K36:L37,K39:L39,K45:L46,K48:L48,K53:L53,K56:L56,K13:L13	Cell	does not contain a blank value	text	NO
I59,K59,K61:L61,I6:L7,I19:L19,I28:L28,I38:L38,I47:L47,I49:L51,I54:L55,I58:L58,G25:H28,I9:M9,M3:T62,G45:H51,G53:H56,G58:H61,G3:H16,G18:H21,G30:L30,I32:L35,I40:L43,G32:H43	Cell	does not contain a blank value	text	NO

 

[StephenCrump]

Welcome to the Forum!

Do you mean like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NEW HIRE - DOES NOT NEED PRIOR MONTH TRAINING COMPLETED						JANUARY 2021 TRAINING SCORE CARD
2	Hire Date	Associate Name		Position	Team	Supervisor	Ammonia Awareness PPT	TRAINER INITIALS	Employee Ammonia Awareness Sign Off	TRAINER INITIALS	Serious Safety Policy
3	44197	Jose	Doe	Worker	1	Jose	43852	JA	43852	JA	43852
4	44197	Joaquin	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
5	44197	John	Doe	Worker	1	Hector	44207		44207	HS	44207
6	44197	Joseph	Doe	Worker	1	Joe	44215	JB	44215	JB	44215		Blanks	Col H	Col J
7	44197	Sally	Doe	Worker	1	Sally	44223				44223		Sally	2	3
8	44197	Raymundo	Doe	Worker	1	Brian	44218	BE	44218	BE	44218		Hector	1	0
9	44197	Brian	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
10	44197	Michael	Doe	Worker	1	Hector	44207	HS	44207	HS	44207		Non-Blanks	Col H	Col J
11	44197	Kapri	Doe	Worker	1	Chris	44221	CG	44221	CG	44221		Sally	1	0
12	44197	Gerardo	Doe	Worker	1	Sally	44221	Sally	44221		44221		Hector	2	3
13	44197	Brian	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
14	44197	John	Doe	Worker	1	Sally	44207				44207
15	44197	Wesley	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
16	44197	Leopoldo	Doe	Worker	1	Hector	44207	hs	44207	hs	44207
17	44197	Santiago	Doe	Worker	1	David	44221	DI	44221	DI	44221
18	44197	Chris	Doe	Worker	1	Chris	44221	CG	44221	CG	44221
19	44197	Kyle	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
20	44197	Peter	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
Sheet1

Cell Formulas
Range	Formula
N7:N8	N7	=COUNTIFS($F$3:$F$20,$M7,H$3:H$20,"")
O7:O8	O7	=COUNTIFS($F$3:$F$20,$M7,J$3:J$20,"")
N11:N12	N11	=COUNTIFS($F$3:$F$20,$M11,H$3:H$20,"<>")
O11:O12	O11	=COUNTIFS($F$3:$F$20,$M11,J$3:J$20,"<>")

 

[Zot]

Not sure I understood. Something like this? Will count non-blank column H

=SUMPRODUCT((F3:F20=N3)*1,(H3:H20<>"")*1)

where N3 is Supervisor name

 

[Stephanie2021]

Not sure I understood. Something like this? Will count non-blank column H

=SUMPRODUCT((F3:F20=N3)*1,(H3:H20<>"")*1)

where N3 is Supervisor name

Close! I would need it to count within G through K. how would I add that range?

 

[Stephanie2021]

Welcome to the Forum!

Do you mean like this?

I would need them to stay dates and have the range of G through K

 

[Stephanie2021]

Not sure I understood. Something like this? Will count non-blank column H

=SUMPRODUCT((F3:F20=N3)*1,(H3:H20<>"")*1)

where N3 is Supervisor name

would this work for range of H through M? or do i have to do every column separately and total them?

 

[StephenCrump]

@Stephanie2021, it looks like we're both unclear on what you want.

Can you please show the results you expect to see?

 

[Stephanie2021]

@Stephanie2021, it looks like we're both unclear on what you want.

Can you please show the results you expect to see?

=SUMPRODUCT((F3:F20=N3)*1,(H3:M20<>"")*1) is what would be amazing, but it is not working

 

[StephenCrump]

=SUMPRODUCT((F3:F20=N3)*1,(H3:M20<>"")*1) is what would be amazing, but it is not working

Sorry, you'll have to explain what you mean by "not working"?

In the example below, Hector appears four times in column F, and for these rows there are three non-blank values in column H.

Hence Zot's formula returns the result 3, which to us appears to be "working", based on what you have described.

If you want different results, please show us what these look like.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NEW HIRE - DOES NOT NEED PRIOR MONTH TRAINING COMPLETED						JANUARY 2021 TRAINING SCORE CARD
2	Hire Date	Associate Name		Position	Team	Supervisor	Ammonia Awareness PPT	TRAINER INITIALS	Employee Ammonia Awareness Sign Off	TRAINER INITIALS	Serious Safety Policy
3	44197	Jose	Doe	Worker	1	Jose	43852	JA	43852	JA	43852			Hector	3
4	44197	Joaquin	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
5	44197	John	Doe	Worker	1	Hector	44207	HS	44207	HS	44207
6	44197	Joseph	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
7	44197	Sally	Doe	Worker	1	Sally	44223				44223
8	44197	Raymundo	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
9	44197	Brian	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
10	44197	Michael	Doe	Worker	1	Hector	44207		44207	HS	44207
11	44197	Kapri	Doe	Worker	1	Chris	44221	CG	44221	CG	44221
12	44197	Gerardo	Doe	Worker	1	David	44221	DI	44221	DI	44221
13	44197	Brian	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
14	44197	John	Doe	Worker	1	Hector	44207	HS	44207	HS	44207
15	44197	Wesley	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
16	44197	Leopoldo	Doe	Worker	1	Hector	44207	hs	44207	hs	44207
17	44197	Santiago	Doe	Worker	1	David	44221	DI	44221	DI	44221
18	44197	Chris	Doe	Worker	1	Chris	44221	CG	44221	CG	44221
19	44197	Kyle	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
20	44197	Peter	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
Sheet2

Cell Formulas
Range	Formula
O3	O3	=SUMPRODUCT((F3:F20=N3)*1,(H3:H20<>"")*1)

 

[Stephanie2021]

Sorry, you'll have to explain what you mean by "not working"?

In the example below, Hector appears four times in column F, and for these rows there are three non-blank values in column H.

Hence Zot's formula returns the result 3, which to us appears to be "working", based on what you have described.

If you want different results, please show us what these look like.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NEW HIRE - DOES NOT NEED PRIOR MONTH TRAINING COMPLETED						JANUARY 2021 TRAINING SCORE CARD
2	Hire Date	Associate Name		Position	Team	Supervisor	Ammonia Awareness PPT	TRAINER INITIALS	Employee Ammonia Awareness Sign Off	TRAINER INITIALS	Serious Safety Policy
3	44197	Jose	Doe	Worker	1	Jose	43852	JA	43852	JA	43852			Hector	3
4	44197	Joaquin	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
5	44197	John	Doe	Worker	1	Hector	44207	HS	44207	HS	44207
6	44197	Joseph	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
7	44197	Sally	Doe	Worker	1	Sally	44223				44223
8	44197	Raymundo	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
9	44197	Brian	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
10	44197	Michael	Doe	Worker	1	Hector	44207		44207	HS	44207
11	44197	Kapri	Doe	Worker	1	Chris	44221	CG	44221	CG	44221
12	44197	Gerardo	Doe	Worker	1	David	44221	DI	44221	DI	44221
13	44197	Brian	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
14	44197	John	Doe	Worker	1	Hector	44207	HS	44207	HS	44207
15	44197	Wesley	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
16	44197	Leopoldo	Doe	Worker	1	Hector	44207	hs	44207	hs	44207
17	44197	Santiago	Doe	Worker	1	David	44221	DI	44221	DI	44221
18	44197	Chris	Doe	Worker	1	Chris	44221	CG	44221	CG	44221
19	44197	Kyle	Doe	Worker	1	Joe	44215	JB	44215	JB	44215
20	44197	Peter	Doe	Worker	1	Brian	44218	BE	44218	BE	44218
Sheet2

Cell Formulas
Range	Formula
O3	O3	=SUMPRODUCT((F3:F20=N3)*1,(H3:H20<>"")*1)

i would like it more than in column H. i would like to include H, I J, ect in the same formula if possible.