Excel - Sum/Count of boolean values

Question

In Excel I am creating a simple scorecard for my Football Pool. Each week a player makes a prediction and the correct guess is in the bottom row.

This is a simplified version of it

Name	Week1	Week2	Week3		Total
Lee	BUF	LAR	TB		3
Beth	BUF	SEA	TB		2
Rick	TEN	LAR	TB		2
Fred	TEN	LAR	PIT		1
Winner	BUF	LAR	TB

My question is about keeping score. To compute the total in Column F I am currently adding the booleans manually:

=(B2=B$7)+(C2=C$7)+(D2=D$7)

This is fine for only three columns of course, but it will definitely get quite cumbersome if there are dozens (or more) columns.

I am wondering if there is a simpler way to accomplish this with a formula such as SUM() or SUMIFS() or COUNTIFS() etc.

Any help would be greatly appreciated. Thanks! :)

Answer 1

Use SUMPRODUCT:

=SUMPRODUCT(--(B2:D2=$B$7:$D$7))