If and Countif with multiple criteria

[JoJoMo]

Hello All,

I have a spreadsheet that need to do a If & countif, however I need the Countif to reset after a certain criteria is met.

Here is the formula I have in AB for Nick that =126, what I need it to do is to reset anytime AFTER the 14 shows up. So it would reset and only count from K-R put that # is R, Then T - AB, put that in AB... my issue is in the other column's this is reading from is a calendar, and the people may not always be in this order. =IF($D23=$G6,14,+COUNTIF(I6:AA6,"0")*7)+14

i

 

[jasonb75]

It's not clear from the picture and description if you want a formula for AB6 or AB23. Showing source data and expected results is preferable (with XL2BB rather than screen captures) as it helps to reduce misinterpretation of the data and any existing formulas.

Best guess on the information that we have would be something like this, array confirmed with Ctrl Shift Enter.

=LOOKUP(1E+100,FREQUENCY(IF(($G$5:$G$15=$D23)*($H$5:H$15=0),COLUMN($H$5:H$15)),IF(($G$5:$G$15=$D23)*($H$5:H$15<>0),COLUMN($H$5:H$15))))*7+14

 

[JoJoMo]

Unfortunally i can't add the plugin , because it's a work computer. the best I can do is expand it so you can see the formulas. I have cleaned it up a little so it's easier to read.

Basically for each row every time the # 28 appears I need a counter to reset and count the cells between the #28 & the following #14 examples are highlighted in green. It will not always be the same number for each person.

 

[jasonb75]

Using David as an example (first row), I would see this

every time the # 28 appears I need a counter to reset and count the cells between the #28 & the following #14

as 2 sets to count, where you have only shown 1 set of green cells for David, L5:Q5 and T5:Z5, so do we go by the description or the picture?
Are the dates in columns A and B somehow related to the dates starting in I4?
Also, you say "between", which implies that the 28 and 14 should both be excluded, yet you show the 28 in green. Again, please confirm which is correct.

Finally (for now) where are the formulas meant to be going? Are you attempting to combine them with the existing formulas as implied in your first post, or are they going elsewhere?