(Excel Mac) COUNTIF ID# is number and listed only once per date
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?
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.
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.
- excel countif entire row in a table
- What is the equivalent of the "Days" function or number of days between 2 dates in excel 2007?
- How to find and replace with regex in excel
- How to change font color for specific line (row) of Textbox
- How to embed a SVG into a Cell using Aspose.Cells
- How to change Font Color of Dates without changing color of other text or background using VBA for Excel
- Run-Time Error 91 (object variable or with block variable not set) on VBA Email code "mailItem.Display" after IF statements
- Deleting duplicate header rows
- mixed date converted from default mm/dd/yy in excel to dd/mm/yy
- Overwrite Excel 2016 worksheet data with data from CSV file
- Laravel validator and excel files error
- Need help aligning a table to a column based on similar values in one table field
- Index rows and columns unexpected results
- Ascii Art using VBA
- How to obtain the full-path names of files read with WorkbookConnections
- How can export all part of columns to pdf file with VBA code?
- Difficult to run
- Allow User Input in Empty Cells, Prevent Editing or Deletion in Occupied Cells
- Export Charts from Excel as images using Python
- Converting 32-bit xll Excel add-in to 64 bit: LNK1561 error
- Changing the Date Format for GROUPED dates in a PIVOT TABLE
- VBA Filter Column Store Unique Values of Another Column
- How do I use checkboxes in an IF-THEN statement in Excel VBA 2010?
- Automatic numbering in Excel with hierarchy
- convert a C# datetime object into Excel date format
- Excel Find and replace using variables
- How to do a row-wise sum in an array formula in Excel?
- Putting Textboxes into array for formatting
- Code inserting and copying to the wrong worksheet
- how to use inequality filter in pivotby formula on aggregated values, not table data