Calculate Subtotal with two conditions on filtered data only

Hi,

I am attempting to calculate subtotals using two conditions. In my worksheet I am using the "Email" column to count the unique employees. Within that count, I also need the number of employees that have "Years in Company" greater than ten. Filtered data only

My issue is:

1) I would like to use the ID column to count the unique employees instead of the email address (the ID is stored as text) and store that result in B15
2) Out of the unique employee count as stated above, I need to calculate the employees in that have greater than 10 "Years in Company" and store the value in B12 (*Years in company is stored as text)

I was able to get the formula to count the unique employees by email:

=SUM(--(FREQUENCY(IF(SUBTOTAL(3, OFFSET(Table1[Email], MATCH(ROW(Table1[Email]), ROW(Table1[Email]))-1, 0, 1)), COUNTIF(Table1[Email], "<"&Table1[Email]), ""), COUNTIF(Table1[Email], "<"&Table1[Email]))>0))

Thanks so much!

Jelx

Hi,

Yes it works perfectly. You are awesome. Thank you so, so very much.