Forum Discussion
Count values with max date in filtered excel table
Hi Zdenec,
Filtered rows - in Excel there are two functions which could ignore hided rows or columns - SUBTOTAL() and AGGREGATE(). To check if row is hided you need to use one of these functions. There are few variants, here we use AGGREGATE() within helper column.
Empty cell counting - sorry, I didn't think about that. If array is empty FILTER by default returns an error, and COUNTA doesn't care that's error or not, counts it as any other value. We may use third parameters in FILTER which will be returned in case of empty array, check if it is returned and take zero as final result, otherwise count number of values in returned array. Resulting formula
=LET( visible, FILTER( T_Data[Name], T_Data[Helper],-1), IF(INDEX(visible,1)<> -1, COUNTA(UNIQUE( visible)), 0) )
HelloSergeiBaklan
I was playing with the empty filter array and I got the idea to filter in a helper cell and then test this cell in the COUNTA. You did it with LET, next function to study for me. But I have a nice example to study on.
Thank You very much for all the support, without your effort I could not finish the task!
You are my Joker.
Zdenek
Zdenek, sure, in Excel everything could be done by several ways, select one which is more comfortable for you.
You are welcome and good luck with your project.