-1

I have 60,000 rows of data where I just pulled out the dates, added columns for day of the week, and have ID #s for each row. Blank ID # cells wouldn't be counted. I need to find the total count by Day of the Week and Quarter but only distinct values on every day.

Is there some way I can do this?

Example Data

I was just using a formula like: =COUNTIFS($A$2:A1000,">="&$M$12,$A$2:$A$1000,"<="&$N$12) to find the dates within the quarters. Earlier I had the references for each quarter date ranges and days of the week too. I am just stuck find distinct counts per day only since I am using Excel on my Mac currently.

Improve this question
2
  • What version of excel are you using? Commented May 17, 2024 at 17:40
  • Excel 365 (for Mac) Commented May 17, 2024 at 17:41

2 Answers 2

Reset to default
1

See below - updated for a more comprehensive formula based on what you have stated in your SO question.

If you need it by each day of the week, you should replace H7:K7 on the worksheet with the ending date of the week and in the formula replace the range reference as well.

Update - basically if it's the same employee number on the same day, the two rows should be identical and you can catch them with a basic unique formula for the range of data. Once you have removed the duplicates with the unique formula, grab the first row with choosecols and then frequency it out over the periods you are analyzing for, dropping the last column and then transposing it for formatting.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

so let me get this - you need to find which employee numbers are on which day, and in which quarter?
I know how to find the total per day of the week and/or quarter, I am just not sure how to include the counts in those ranges if the ID # is there (is a number) and is not listed more than once per date (if ID # 9876987 is listed twice for 10/2/2023 it should only count the 1st occurrence). Basically distinct count I think, but available on Mac.
I just need a count of all employee numbers for each day of the week within a quarter, which I can do, but only counting distinct ones for each specific date.
ok - check out above. I combined a bunch of formulas.
Could I adjust that formula to allow me to get a total count for periods, not just by every employee number? As there are over 450 unique employee numbers and I just need basically something like the total attendance per day of week per quarter. While ignoring duplicate instances of employees coming in and out the same day.
|
0

Power Query is available under Mac. But here's a way without it.

First I would suggest creating a table for your data, then in cell with enough rows to for the result, enter the following:

=LET(
distinct_count, LAMBDA(array, ROWS(UNIQUE(array))),
quarter, LAMBDA(date,ROUNDUP(MONTH(date)/3,0)),

dates, Table1[DateTime of Transaction],
ids, Table1[EmpNo],

distinict_dates, UNIQUE(dates),
non_blank_ids_for_date, LAMBDA(date, NOT(ISBLANK(ids))*(dates=date)),
distinct_id_count_for_date, LAMBDA(date, distinct_count(FILTER(ids, non_blank_ids_for_date(date)))),

distinct_id_counts_for_dates, MAP(distinict_dates, distinct_id_count_for_date),
VSTACK({"Date","Emp Count","Year","Quarter"},
HSTACK(distinict_dates,distinct_id_counts_for_dates, YEAR(distinict_dates), quarter(distinict_dates))
))

This will get unique employee count for each date. This can be used as a basis for a pivot table to get your total for week/quarter etc.

Sample result with pivot table

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.