0

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?

  • 2
    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
1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.