counting problem

[cwheeler116]

Here is my data set
A B C
1 FALSE FALSE
2 TRUE FALSE 1
3 TRUE FALSE 2
4 TRUE FALSE 3
5 TRUE FALSE 4
6 TRUE FALSE 5
7 FALSE FALSE
8 FALSE FALSE
9 TRUE FALSE 1
10 FALSE FALSE
11 FALSE FALSE

The formula in column C is =IF(AO44=TRUE,ROW()-LOOKUP(10^99,ROW(AO$6:AO44)/(AO$6:AO44=FALSE)),"")

My issue is that in C7 there is a blank because both A7 and B7 are False, however if BOTH are false I need the count to continue so that C9 would be an output of 6

[JBeaucaire]

C1: =IF(AND(A1, NOT(B1)), 1, "")
C2: =IF(AND(A3, NOT(B3)), MAX($C$1:$C2)+1, "")

Copy C2 downward.

[JBeaucaire]

Or if the count goes up if EITHER A or B is true, then maybe:

C1: =IF(SUM(--A1,--B1)>0, 1, "")
C2: =IF(SUM(--A2,--B2)>0, MAX($C$1:$C1)+1, "")

Copy C2 downward.


NOTE: The -- is turning the TRUE and FALSE into 1 and 0.

[cwheeler116]

the formula looks to work, however it shows a value of zero. When i click on the function to check the formula result, it shows a value of 1

[JBeaucaire]

You have a workbook for me to look at?

[cwheeler116]

here is my actual data (see attachment)

Look at columns AO, AP, and AQ on the Data tab.

For column AQ, i need a formula that looks at both AO and AP. If AO6 is True, then AP6 will be false. AQ6 needs to have an output of 1. If both AO6 and AP6 are false, the AQ6should be 0. Starting at AQ7, then if AO7 is true then AQ7 would be 1+AQ6. However if at any time there is a TRUE in column AP, then the count in AQ would stop and start the next time there is a TRUE in column AO.

[JBeaucaire]

the first formula in AQ7 should be: =IF(SUM(--AO7,--AP7)>0, MAX($AQ$6:$AQ6)+1, 0)

then copy down.

[cwheeler116]

but what about stopping the count if AP is True?

[JBeaucaire]

I gave you two versions. The first one is for TRUE/FALSE only, the second one worked for TRUE/FALSE, TRUE/TRUE or FALSE/TRUE.

Use the first one from post #2.

[cwheeler116]

can you take a look? I input the formulas and not getting the outputs correct

attached is workbook with formulas input from post #2

[cwheeler116]

i got that formula to count, however if you look at cell AP44, it shows true so the count in AQ47 needs to be 1 instead on continuing to count and being 5.

[JBeaucaire]

You keep making the same error over and again. The MAX() reference can't include the cell the formula is in, it has to stop ABOVE the cell.

So the first cell formula in AQ6 could be: =IF(AND(AO6, NOT(AP6)), MAX($AQ$5:$AQ5)+1, 0)

[cwheeler116]

i understand the above formula now with the MAX function, however i still need the count to start over if the value in AP is ever True

[JBeaucaire]

Hmm.... missed that entirely. Are the rows the increment always together?

[cwheeler116]

what do you mean always incremental? There may be 5 rows where you have either True or false in AO and AP respectively, and then another 5 where both would be false. If you still have the workbook, unhide all of the columns on the right and you will see the logic. The data is entered on the far left of the DATA tab, and based upon the column (I think it is D) that is called EST #, all of the columns that are hidden look for the EST number in order to know if they populate True or False.

[JBeaucaire]

I'm not coming up with a simple solution to restart the counting after a False/True row occurs. I was asking if the rows that get the incremental numbers always appear TOGETHER, your example data is short, only shows one group of 5 then another group of 1 with a FALSE/TRUE splitting them. I was wondering if there would ever be 0s in the list and then the prior list continued again, or does it typically always start over again after the 0s start?

[cwheeler116]

here is the logic for this:

IF True False then count, if another True False then count increases
If False False then skip
If False True then start count at next True False

[cwheeler116]

I am attaching a sample larger data set. In the blank areas under the heading pass count and fail count, I can insert a zero instead of a blank if that would be helpful. The sample data in the attachment would represent how it needs to be reflected in columns C and D.

[JBeaucaire]

In C2 and copied down:

=IF(A2,IF(IFERROR(MATCH(99^99, $C$1:$C1, 1),0)>IFERROR(MATCH(99^99, $D$1:$D1, 1),0), INDEX($C$1:$C1, MATCH(99^99, $C$1:$C1,1))+ 1, 1), "")

[cwheeler116]

works, thank you very much

[cwheeler116]

Can i get you to look at one more item,

Using the following formula

=IF(AO77,IF(IFERROR(MATCH(99^99, $AQ$5:$AQ76, 1),0)>IFERROR(MATCH(99^99, $AR$5:$AR76, 1),0), INDEX($AQ$5:$AQ76, MATCH(99^99, $AQ$5:$AQ76,1))+ 1, 1), "")

I also need in this formula to say that if AP77=True, then in AQ77 will be zero.

[JBeaucaire]

Just add new stuff like that at the beginning.

=IF(AP77, 0, IF(AO77,IF(IFERROR(MATCH(99^99, $AQ$5:$AQ76, 1),0)>IFERROR(MATCH(99^99, $AR$5:$AR76, 1),0), INDEX($AQ$5:$AQ76, MATCH(99^99, $AQ$5:$AQ76,1))+ 1, 1), ""))

[cwheeler116]

figured that out after i posted this, thanks again