• 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.

Excel Formula Question (How to count 7 days in a row)

Hi there,

Good day! Hope you are well. I am pretty knew to this forum.
I have a question and attached the file for your review.

I would need assistance in counting letter B from Column B to GD.
But the trick is I would only need to count B if its 7 days in a row or more then minus 184 constant value.

IF B doesn't have 7 days in a row it should not be counted.

In the attached file Employee 1 do have a lot of B from Column B to GD but the total that I need to count would only be 12 since Column FS to GD had 7 days or more in a row.

Hope everything is clear
 

Attachments

  • Sample Data.xlsx
    17 KB · Views: 6
In the attached, in Sheet2, a table created with Power Query. See column GD being a total count of cells containing B but only when in groups of 7 or more consecutively.
You can right-click the table and click Refresh after the data in the table on Sheet1 has been changed/expanded.
I haven't subtracted 184 because it would result in negative numbers, but if you want this to happen, say so.
 

Attachments

  • Chandoo44926Sample Data.xlsx
    54.9 KB · Views: 3
Hi there,

Thanks for sending. This is great however I am looking for a simple solution if there are any as the user of the file will be an entry level.
It will be hard for them to use a power query
 
Not an especially simple formula in cell GF2 of the attached:
Code:
=SUMPRODUCT(IF(FREQUENCY(IF(B2:GC2="B",COLUMN(B2:GC2)),IF(B2:GC2<>"B",COLUMN(B2:GC2)))>6,FREQUENCY(IF(B2:GC2="B",COLUMN(B2:GC2)),IF(B2:GC2<>"B",COLUMN(B2:GC2))),FALSE))
which may need to be committed to the sheet with Ctrl+Shift+Enter instead of the more usual simple Enter,depending on your version of Excel. Then copy down.
 

Attachments

  • Chandoo44926Sample Data_b.xlsx
    18.2 KB · Views: 7
I agree, the solution is impressive. To understand the formula, I did some refactoring and introduced names, which is the only way I can understand formulas. In passing, I noticed that the column number was used for the frequency calculations, though the date would serve the same purpose and is more directly related to the problem definition.
Code:
=SUMPRODUCT(IF(FREQUENCY(IF(B3:GC3="B",B$1:GC$1),IF(B3:GC3<>"B",B$1:GC$1))>6,FREQUENCY(IF(B3:GC3="B",B$1:GC$1),IF(B3:GC3<>"B",B$1:GC$1)),FALSE))
or
=SUMPRODUCT(IF(FREQUENCY(IF(empData="B",date),IF(empData<>"B",date))>6,FREQUENCY(IF(empData="B",date),IF(empData<>"B",date)),FALSE))

Out of interest, I then went flat out to move to 'modern Excel' with FILTER to eliminate the FALSEs from lists and LET to provide locally defined names and to reduce repeat calculation. I finished up with
Code:
= LET(
  freq, FREQUENCY(
      FILTER(date, empData="B"),
      FILTER(date, empData<>"B")),
  prolonged, FILTER(freq, freq>6, 0),
  SUM(prolonged) )
 
Back
Top