I have a set of data looks like this:
FALSE
FALSE
FALSE
FALSE
1 <--
1
1
1
FALSE
FALSE
FALSE
FALSE
1 <--
1
1
1
FALSE
FALSE
FALSE
FALSE
My question is how do I count the total of the first 1
in the column?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI have a set of data looks like this:
FALSE
FALSE
FALSE
FALSE
1 <--
1
1
1
FALSE
FALSE
FALSE
FALSE
1 <--
1
1
1
FALSE
FALSE
FALSE
FALSE
My question is how do I count the total of the first 1
in the column?
I'm not sure this is the best way to deal with this question, but it worked perfectly fine in my test.
Assuming you only have the values FALSE
and 1
in this particular column and that this column is A
, and the first value is at A2
. Put the following formula in B2
(or any cell you like, but you will need to modify the formula).
=IF(A2<>A1, IF(A2=1, "1", "not 1"), "not 1")
This will compare the value to the left of the formula with the value above it, if they match it gets ignored. If they don't match then it must be either the first FALSE
or 1
value in the block.
The nested IF
statement then determines if this value is a FALSE
or 1
. If it is FALSE
it gets ignored and marked as not 1
and if it is a 1
, then it's marked as 1
. We'll use this value later.
You will then need to copy this down the entire length of your spreadsheet (remember when I said this may not be the best?).
Once you have your test column values all figured out, a simple COUNTIF
can be used to count the number of matched cells that contain 1
.
Usage:
=COUNTIF(RANGE, CONDITION)
Example:
If your data is in column A
:
=COUNTIF(A1:A41, 1)
Hope this does what you need!
EDIT: If you want a nice tidy looking reference column you can use the following to only pick out the first 1
of each block:
=IF(A2<>A1, IF(A2=1, "1", ""), "")
For Excel >= 2007 only:
=COUNTIFS(A1:A1048575, "=FALSE", A2:A1048576, "=1")+N(A1=1)