Frequency formula help

TS2021

New Member
Joined
Oct 26, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hope everyone is well.

I learn so much from these forums. I truly cant thank you folks enough!

Hope you can help me.

Goal: To find total number of consecutive days off each employee has taken. So if an employee has taken days off on Thurs n Fri then count as 1. If Thurs- Fri- Sat then count should be 2. So in the example provided below count of consecutive days off should be 18.
Data: F3:AJ3 (3 through 2000)
Formula applied: =MAX(FREQUENCY(IF(F3:AJ3>1,ROW(F3:AJ3)),IF(F3:AJ3<1=0,ROW(F3:AJ3)))) in AM3 for total days off taken by employee XYZ and =COUNTIF(F3:AK3,"OFF")-SUMPRODUCT(--(F3:AJ3="OFF"),--(F3:AJ3<>G3:AK3),--(G3:AK3=H3:AL3)) in AN3 for total instances of consecutive days off taken.
AM3 should be providing consecutive instances for days taken off but when its copied 3 through 2000 some rows have incorrect consecutive instances. Goal is to ensure that AN reflects total number of consecutive days off taken by each employee 3 through 2000.

I would truly appreciate your help. Thanks a ton Excel Experts :)

1635269289389.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
Based on that, I believe that @bebo021999's suggestion in post 5 will be the correct solution. If it is not giving you the results that you need then we would need to see some examples of where it is giving the wrong result, along with the result that you expect in order to understand why.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,141
Office Version
  1. 2016
Nice to see it works. It could be better if using VBA code.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top