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,620
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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,152,115
Messages
5,768,204
Members
425,459
Latest member
Danniey

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