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

James Carlo Cruz

Member
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

• 17 KB Views: 6

p45cal

Well-Known Member
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

• 54.9 KB Views: 3

James Carlo Cruz

Member
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

p45cal

Well-Known Member
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

• 18.2 KB Views: 6

James Carlo Cruz

Member
Hi p45cal, thanks this is awesome. I like it! Thank you so much

Peter Bartholomew

Well-Known Member
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) )``````

Excel Wizard

Member
or try
=SUM(--TEXT(FREQUENCY(IF(B2:GC2="B",B\$1:GC\$1),IF(B2:GC2<>"B",B\$1:GC\$1)),"[>6]0;\0"))
Confirm by Ctrl+Shift+Enter

Attachments

• 18.4 KB Views: 4

James Carlo Cruz

Member
Wow guys thank you so much you are really great in this kinds of stuff.

Peter Bartholomew

Well-Known Member
@Excel Wizard
Neat trick to avoid repeating the frequency calculation.
I am not sure I would have thought of using number formatting like that as part of the formula.