• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count first 8 Rows

Adnan Halai

New Member
Hello Experts,

I am working on plant manufacturing sheet where in I am working to calculate formula for below logic.

If any of the first 8 "Batches" on each day is the value of 100 then the "Cycle" column should be marked at that point with a 1. Otherwise if none of the first 8 results on each day have a "Batches" with the value of 100 then the 8th result should be marked with a 0. All the rest should be blank.

Attached is the spreadsheet for reference.
 

Attachments

  • Chandoo.xlsx
    11.2 KB · Views: 9
Last edited by a moderator:
Try this:

=IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,100)=1,B2=100),1,IF(AND(COUNTIFS(A$2:A2,A2)=8,COUNTIFS(A$2:A2,A2,B$2:B2,100)=0),0,""))
 
@Luke M, a tweak perhaps; I tried your formula but when I altered cell B6 from 100 to say 200, which makes the first occurrence of 100 for 1/1/2017 the 9th for that date at cell B10, I got a 1 in row 10 and a 0 above it.

The following might go towards taking the glitches out:
=IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,100)=1,B2=100,COUNTIF(A$2:A2,A2)<9),1,IF(AND(COUNTIFS(A$2:A2,A2)=8,COUNTIFS(A$2:A2,A2,B$2:B2,100)=0),0,""))
 
Back
Top