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

 

[Eric W]

I'm kind of late to the party, but try this variation of Zot's formula:

=SUMPRODUCT((F3:F20=N3)*(H3:J20<>""))

 

[Stephanie2021]

I'm kind of late to the party, but try this variation of Zot's formula:

=SUMPRODUCT((F3:F20=N3)*(H3:J20<>""))

BEAUTIFUL! it worked
thank you everyone for your assistance!!

 

[StephenCrump]

I'm kind of late to the party ....

... but with impeccable timing!

@Stephanie2021, I was guessing it would make more sense to count only columns H and J (perhaps only if there were also corresponding dates in G and I?)

That way you are counting only the number of training tasks completed by the trainer, rather than a mix of initial and date fields?

 

[Stephanie2021]

... but with impeccable timing!

@Stephanie2021, I was guessing it would make more sense to count only columns H and J (perhaps only if there were also corresponding dates in G and I?)

That way you are counting only the number of training tasks completed by the trainer, rather than a mix of initial and date fields?

I understand the redundancy, but there is actually an average of 75 or better columns. I much rather divide by 2 than have to set up individual column formulas. If there is a way to add in a count for every other I am all for it!

thank you so much for the follow up

 

[StephenCrump]

Yes, you can pick up every 2nd column using a formula.

I'll post back later (but secretly hoping Eric W beats me to it).

 

[Zot]

BEAUTIFUL! it worked
thank you everyone for your assistance!!

Just what I would have guess. Credit to Eric.
Stephanie, we are on different time zone ?

 

[Eric W]

I'll post back later (but secretly hoping Eric W beats me to it).

LOL! ?

It's not a big change:

Book3
	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							Hector	8
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	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	HS	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
Sheet1

Cell Formulas
Range	Formula
O1	O1	=SUMPRODUCT((F3:F20=N1)*(H3:J20<>"")*(MOD(COLUMN(H3:J3),2)=0))

Note the part in red:
=SUMPRODUCT((F3:F20=N1)*(H3:J20<>"")*(MOD(COLUMN(H3:J3),2)=0))

I don't know how much you know about MOD. In this case, H is an even column (8), I is odd (9), and J is even (10). MOD turns those column numbers into 0,1,0. And the formula only looks at the columns that become 0. If you want the odd columns, change the "=0" to "=1". If you use

(MOD(COLUMN(H3:J3),3)=0)

instead, you can look at only every third column.

 

[Stephanie2021]

Just what I would have guess. Credit to Eric.
Stephanie, we are on different time zone ?

LOL

 

[Stephanie2021]

LOL! ?

It's not a big change:

Book3
	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							Hector	8
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	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	HS	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
Sheet1

Cell Formulas
Range	Formula
O1	O1	=SUMPRODUCT((F3:F20=N1)*(H3:J20<>"")*(MOD(COLUMN(H3:J3),2)=0))

Note the part in red:
=SUMPRODUCT((F3:F20=N1)*(H3:J20<>"")*(MOD(COLUMN(H3:J3),2)=0))

I don't know how much you know about MOD. In this case, H is an even column (8), I is odd (9), and J is even (10). MOD turns those column numbers into 0,1,0. And the formula only looks at the columns that become 0. If you want the odd columns, change the "=0" to "=1". If you use

(MOD(COLUMN(H3:J3),3)=0)

instead, you can look at only every third column.

you gentlemen are changing my life! I did not know of MOD! I will definitely play with this one