I came across a formula to find Distinct Values using SUMPRODUCT and FREQUENCY functions. Ref. : ExcelIsFun - Excel Magic Trick 1337 I recreated the situation. 1. I used COUNTA + UNIQUE - I got the same answer as in the screenshot above. 2. I used SUMPRODUCT + FREQUENCY - My answer is 267 (the answer is 268) My Q. Please help Me understand why My 2nd formula is not producing the same result. Observation: I used only FREQUENCY formula to understand the reason. I noticed since October the values in column#D shifted down by 1 cell i.e. initially FREQUENCY formula produced the count in front of the 1st distinct date. Since October, the FREQUENCY formula produced the count 1 cell down. NOTE - I have an Office 365 Subscription. A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. Link: MS_Community_20July2022.xlsx No problem. :-) I can not explain exactly why, but the issue is related to your data. If you change the cells in both functions to A6:A210 you'll get the same result. From A6:A211 on you get one less. The reason is in A211 which contains 29-02-2021 which is a text, because this day did not exists. All other cells contains a real date, means a number.

Why FREQUENCY has problems with it... please investigate this yourself. :-) Possibly due to the comments / special features of this function documented here: https://support.microsoft.com/en-us/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9 3 additional answers Anonymous 2022-07-20T11:12:39+00:00 . . . I can not explain exactly why, but the issue is related to your data. If you change the cells in both functions to A6:A210 you'll get the same result. From A6:A211 on you get one less. The reason is in A211 which contains 29-02-2021 which is a text, because this day did not exists. All other cells contains a real date, means a number. Why FREQUENCY has problems with it... please investigate this yourself. :-) Possibly due to the comments / special features of this function documented here: https://support.microsoft.com/en-us/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9 . . . @Andreas Killer, Thank You Very, Very Much for Your Expert Observation! 0 comments No comments . . . I can not explain exactly why, but the issue is related to your data. If you change the cells in both functions to A6:A210 you'll get the same result. From A6:A211 on you get one less. The reason is in A211 which contains 29-02-2021 which is a text, because this day did not exists. All other cells contains a real date, means a number. Why FREQUENCY has problems with it... please investigate this yourself. :-) Possibly due to the comments / special features of this function documented here: https://support.microsoft.com/en-us/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9 @Andreas Killer, Thank You Very, Very Much for Your Expert Observation! Anonymous 2022-07-20T07:59:52+00:00 You've actually been in this forum long enough IMHO to know that an answer to this type of question requires a sample file. ;-) Andreas. Edited - added ExcelIsFun's workbook too. Apologies! Link: MS_Community_20July2022.xlsx Link - ExcelIsFun's Workbook: MS_Community_20July2022_ExcelIsFun_Ref_Workbook.xlsx Thank You! 0 comments No comments You've actually been in this forum long enough IMHO to know that an answer to this type of question requires a sample file. ;-) Edited - added ExcelIsFun's workbook too. Link: MS_Community_20July2022.xlsx Link - ExcelIsFun's Workbook: MS_Community_20July2022_ExcelIsFun_Ref_Workbook.xlsx Andreas Killer • 144.1K Reputation points • Volunteer Moderator 2022-07-20T07:40:20+00:00 You've actually been in this forum long enough IMHO to know that an answer to this type of question requires a sample file. ;-) Andreas. 0 comments No comments You've actually been in this forum long enough IMHO to know that an answer to this type of question requires a sample file. ;-)