TransWikia.com

How can I write a formula that shows distinct values in a date range?

Super User Asked by L.Townsend on November 7, 2021

I am working on a spreadsheet that lists every time a student interacts with our service. In column F we list the student ID number. I need to know how many different (distinct) students we have interacted with in different date ranges. The spreadsheet has lots of blanks due to students being unwilling to give us their ID number.

Currently I have the formula:

=SUM(IF(FREQUENCY(F2:F10000,F2:F10000)>0,1)) 

I need to add a date range to this. Start date is currently in AB3 and end date is AC3. Dates we interacted with students are currently listed in M column. I cannot type the date range directly into the formula as this is going to be used by people with limited computer literacy (even more limited than me) and so it needs to be set up so they do not have to touch the formulas at all.

Data is set out like image below. We often get multiple students with the same name so we rely on ID numbers. The actual number of lines of data is in the thousands. Obviously, data in image is fake but hopefully it will give you some idea. The formulas start after AB and the only thing that is changed is the date range we want to see.
Image of data layout

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP