Count number of times "1" appears more than twice in a row and if max count is less than 4 - include "1" where no consecutives - Excel

0
votes
Count number of times "1" appears more than twice in a row and if max count is less than 4 - include "1" where no consecutives - Excel - Stack Overflow
Asked
Active 10 months ago
Viewed 35 times
0

Follow up question to Count number of times value appears consecutively in a row - Excel

I want to count the number of "1"'s that appear more than once consecutively in a row - and if max count for that row is less than 4, then also count 1's with no consecutives.

enter image description here

Below output is correct until single "1" which is not counted. Countif formula is =AND(E5=1, F5=1) + COUNTIFS(E5:Z5, "", F5:AA5, 1, G5:AB5, 1) which recognizes consecutive "1"s. If row 3 had a single 1 at the beginning, its value would be 3 because counter (bold) is only 2.

3
  • Include them all or only enough to reach 4?
    – Scott Craner
    Apr 12 2021 at 21:00
  • Only enough to reach 4
    – Zachary Wyman
    Apr 12 2021 at 21:05
  • In your original question you gave the following example" [0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, 0, 1], the total count is 3 which is less than 4, so could you please advise what would be the desired count based on your new requirement? Could you please in your example include a couple different scenarios and the desired output?
    – Terry W
    Apr 13 2021 at 0:32
1

Use:

=AND(E1=1,F1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,1)+MAX(MIN(4-(AND(E1=1,F1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,1)),(E1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,"")+AND(AB1="",AC1=1)),0)

enter image description here

    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.

    Your privacy

    By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

     
    1
    excelexcel-formula
    Include them all or only enough to reach 4? - Scott Craner
    Only enough to reach 4 - Zachary Wyman
    In your original question you gave the following example" [0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, 0, 1], the total count is 3 which is less than 4, so could you please advise what would be the desired count based on your new requirement? Could you please in your example include a couple different scenarios and the desired output? - Terry W

    1 Answers

    1
    votes

    Use:

    =AND(E1=1,F1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,1)+MAX(MIN(4-(AND(E1=1,F1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,1)),(E1=1)+COUNTIFS(E1:AA1,"",F1:AB1,1,G1:AC1,"")+AND(AB1="",AC1=1)),0)

    enter image description here