# Count function in excel

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?

• Heya, I hate to say this but your question is terribly formatted. I'm not even sure if the proposed edit is correct. Could you check and approve it if it is, and/or use codeblocks? – Journeyman Geek Jul 28 '15 at 23:15

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", ""), "")`

• Thanks Michael for your reply. I need to count the first "1" that occurs in a role of "1"s that separated by multiple "False". so there maybe 118 first "1"s but total of "1" might be 10000. i don't know if that make sense. – Sophia Liu Jul 28 '15 at 23:22
• Oh right! My mistake, give me a bit and I'll work this out. – Michael Frank Jul 28 '15 at 23:24
• @SophiaLiu Hi Sophia, I've updated my answer with a method that should work for you. – Michael Frank Jul 29 '15 at 0:11
• Hi, Michael Thanks a lot. The formula works great. – Sophia Liu Jul 29 '15 at 21:01
• @SophiaLiu If this answer works for you, please mark it as accepted by checking the green tick. – Michael Frank Jul 29 '15 at 21:03

For Excel >= 2007 only:

``````=COUNTIFS(A1:A1048575, "=FALSE", A2:A1048576, "=1")+N(A1=1)
``````
• This doesn't seem to work correctly in Excel 2010 (at least). It gives me a total one less than the actual number of blocks. – Michael Frank Jul 29 '15 at 21:05
• Maybe you started the test column with a 1's block? Excel 2010 here too, it does work as expected. – SΛLVΘ Jul 29 '15 at 23:28
• What if the users spreadsheet starts with a block of 1's? – Michael Frank Jul 29 '15 at 23:30
• Your solution wouldn't work either... I followed the given example. – SΛLVΘ Jul 29 '15 at 23:34
• My method works perfectly fine: i.stack.imgur.com/UuvcS.jpg – Michael Frank Jul 29 '15 at 23:43