Table2[TASK]="PSHP" or "SHIP"
To count unique values within a date range, you can apply a formula.
Select a cell that you will place the counted result, and type this formula =SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0)), press Shift + Ctrl + Enter to get the correct result. See screenshot:
Tip: in the above formula, B2:B8 is the date cells in your data range, E1 is the start date, E2 is the end date, A2:A8 is the id cells you want to count unique values from. You can change these criteria as you need.
To count the unique values between two given dates, you also can apply Kutools for Excel’s Select Specific Cells utility to select all values between the two dates, and then apply its Select Duplicate & Unique Cells utility to count and find the unique values.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Select the date cells and click Kutools > Select > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog, check Entire row option in Selection type section, select Greater than or equal to and Less than or equal to from the two drop down lists separately, check And between two drop down lists, and enter start date and end date separately in the two textboxes in Specific type section. See screenshot:
3. Click Ok, and a dialog pops out to remind you the number of selected rows, click OK to close it. Then press Ctrl + C and Ctrl + V to copy and paste the selected rows in another location. See screenshot:
4. Select the id numbers from the pasted range, click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:
5. In the popping dialog, check All unique (Including 1st duplicates) option, and check Fill backcolor or Fill font color if you want to highlight the unique values, and click Ok. And now another dialog pops out to remind you the number of unique values, and at the same time the unique values are selected and highlighted. See screenshot: