count if a value appears in a range of cells

[lshad24]

I have a gradebook that contains 3 assignments. For each assignment, a student could choose between one of three options (A, B, or C). Some students completed multiple option.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1A[/TD]
[TD]1B[/TD]
[TD]1C[/TD]
[TD]2A[/TD]
[TD]2B[/TD]
[TD]2C[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I need to count how many assignments were done, not how many options they completed. So in the example above, John complete 2 assignments.
Mary completed 2 assignments.
Sue completed 1 assignment.

What is the correct formula that will look at a range of cells, and if a student completed at least one option, return a count of 1?

Thanks for any guidance you can provide.

 

[jscranton]

Put this in column H and drag down.

=(IF(SUMPRODUCT(B2:D2)>1,1,0))+(IF(SUMPRODUCT(E2:G2)>1,1,0))

 

[lshad24]

RESOLVED Re: count if a value appears in a range of cells

Put this in column H and drag down.

=(IF(SUMPRODUCT(B2:D2)>1,1,0))+(IF(SUMPRODUCT(E2:G2)>1,1,0))

jscranton, this was exactly what I needed. Thanks for your quick reply!