Counting Occurrences

alm395

New Member
Joined
Apr 23, 2018
Messages
21
I have a table with employees down column A and dates going across (3/20/20 - 4/4/21). Each date is tracked with that person's daily status. I already have a section that counts the totals (number of days) of each status type, but am now looking to find the number of times/occurrences each person has taken a specific status code. I needing help with the formula for column V. This will be done across multiple tabs in one workbook that is tracking approximately 1100 employees, if that makes any difference.

Example:
Status Code "Out Of Office"

PERSON 1: Total Days=6 / # Occurrences=2
PERSON 3: Total Days=1 / # Occurrences=1
PERSON 6: Total Days=12 / # Occurrences=3
PERSON 8: Total Days=5 / # Occurrences=4

Any help is appreciated because I have not been able to figure this out. Thank you in advance!

Min Max Avg Days.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Out of Office
2Full Name12/28/202012/29/202012/30/202012/31/20201/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/20211/9/20211/10/20211/11/20211/12/20211/13/20211/14/20211/15/2021TOTAL DAYS# OCCUR
3PERSON 1ActiveOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive62
4PERSON 2ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActiveDay OffDay OffOut Of OfficeOut Of OfficeActiveActiveActive21
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeDay OffDay OffActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeActive52
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive00
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveOut Of OfficeOut Of OfficeOut Of Office93
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveOut Of OfficeOut Of Office123
9PERSON 7ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive00
10PERSON 8ActiveActiveOut Of OfficeActiveOut Of OfficeDay OffDay OffOut Of OfficeActiveOut Of OfficeOut Of OfficeActiveDay OffDay OffActiveActiveActiveActiveActive54
11PERSON 9ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActiveDay OffDay OffActiveOut Of OfficeOut Of OfficeActiveActive21
Sheet1
Cell Formulas
RangeFormula
U3:U11U3=COUNTIF(B3:T3,$U$1)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Try
Excel Formula:
=COUNTIFS(B3:T3,$U$1,C3:U3,"<>"&$U$1)
 
Solution

alm395

New Member
Joined
Apr 23, 2018
Messages
21
Try
Excel Formula:
=COUNTIFS(B3:T3,$U$1,C3:U3,"<>"&$U$1)
Did you know that just off the top of your head?! I swear...I thought I tried everything and that just worked like a charm!!! THANK YOU!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,019
Members
416,006
Latest member
PCaffrey

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