Forum Discussion
Counting text only once within date range
Hello,
I'm struggling to create a formula that will count text within a column only once, but also falls within a certain date range i.e. 01/01/2025 - 31/01/2025.
I'm able to create the formula to either count the text once or to look up date within the given date range, but I don't know how to make them work together. Can someone help me create something, please?
This is an example spreadsheet, as I can't post the actual one due to confidentiality, but I'd like the formula to tell me how many clinical areas we have during 2025, for example. So the answer using this example should be 2.
Please let me know if you need more information to help me!
Thank you :-)
5 Replies
The solution depends in part upon what you mean by "we have".
See the attached workbook.
hard without a 'bigger' sample as you didn't even need the UNIQUE part. I will assume the UNIQUE should be on the Company column:
=ROWS(UNIQUE(FILTER(Table1[Company], (Table1[Date]>=DATE(2025,1,1) )*(Table1[Date]<=DATE(2025,12,31) ),NA())))Amazing! That's worked. Thank you so much! :-D
Hi,
Thanks for coming back to me so quickly.
Looking back at my post, I didn't explain what I'm trying to get out of this, so sorry!
I'm trying to create a formula that will only count the 'Clinical Area' text only once, but will only include them where they fall within a certain date.
So, for example, I'd like the formula to look up how many clinical areas there were between 01/01/2024 - 31/12/2024. I've given you a slightly bigger sample below, as you mention the above wasn't big enough.
For clinical areas in 2024 the answer to the formula should be 2 using the data below. Although there are 4 clinical areas in total, only 2 of those were contracted in 2024 (although MS occurs twice, I only want it to be counted once).
I hope that makes more sense and you can still help me! :-)
Thank you.
=IF(SUM(--(YEAR(Tabelle1[Year Contracted])=2024))=0,"nothing",COUNTA(UNIQUE(FILTER(Tabelle1[Clinical Area],YEAR(Tabelle1[Year Contracted])=2024))))Or you could do it with a pivot table (distinct count).
https://www.excelcampus.com/pivot-tables/distinctcount-pivot-tables/
