How to count occurrences of specific element in multiple columns in Excel?

Question

I have this table in Excel:

I want to count number of zeros and ones in each row and put it in corresponding columns (Number of ones, Number of zeros)

So, for row 3:
There are 3 ones and no zeros.
So in column D (Number of ones) will be 3 and in column E (Number of zeros) will be 0.

How to achieve that using Excel formula in D and E columns?

Peter H · Accepted Answer · 2016-10-13 05:41:52Z

1

In Excel you can use the COUNTIF Function to accomplish This.
In Cell D3 enter

=COUNTIF(A3:C3,1)

This first argument identifies the range A3 to C3... The second argument basically identifies what you want to count, in this case the number of ones.

Likewise to count the number of zeros.... In Cell E3 enter the formula

=COUNTIF(B3:D3,0)

You can then fill down the formula (By double clicking on the corner of the rectangular box)

edited Oct 13, 2016 at 5:41

answered Oct 13, 2016 at 5:35

Peter H

9011 gold badge10 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to count occurrences of specific element in multiple columns in Excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related