Forum Discussion

Copper Contributor

Jun 30, 2025

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 ...

excel

Formulas and Functions

m_tarler

Bronze Contributor

Jun 30, 2025

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())))

SmilesTMS

Copper Contributor

Jul 01, 2025

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.

Detlef_Lewin
Silver Contributor
Jul 04, 2025
=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/