How To Count A Row Only If It Contains 1 In Excel

Question

I have data in Excel where each row represents a driver's log. Within each row there are 5 columns that represent 5 possible types of violations.

I need to get the driver's percentage of logs with violations. To do that I need to count the number of rows with a non-zero number in one of the columns. I am looking for a way for excel to do this for me.

I would like to add a 6th column and call it violation count. If there is a non-zero number in the row I would like Excel to add a 1 to the violation count column. Then I would be able to sum that column to get the total number of logs with violations.

Could someone tell me how to do that? Or at least give me the right keywords to search?

Answer 1

You want to wrap a COUNTIF in an IF:

=IF(COUNTIF(B2:F2,">=1"),1,0)

Answer 2

@Scott Craner's answer works well. Here are a few other options

=IF(SUM(B2:F2),1,0)

similarly

=--(SUM(B2:F2)>0)

or even this, if you are happy to use a CSE formula:

=OR(--B2:F2)

enter this formula using Ctrl+Shift+Enter

---

Update

To leave out the cells with values greater than 1

=--(COUNTIF(B2:F2,1)>0)

or

=--OR(A1:B1=1)

the latter again using Ctrl+Shift+Enter