SOLVED

Count values with max date in filtered excel table

%3CLINGO-SUB%20id%3D%22lingo-sub-2200921%22%20slang%3D%22en-US%22%3ECount%20values%20with%20max%20date%20in%20filtered%20excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2200921%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20colleagues%3C%2FP%3E%3CP%3EI%20still%20record%20Covid%20tests%20in%20an%20excel%20table%3C%2FP%3E%3CTABLE%20width%3D%22190%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2244%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2275%22%3ETest%20Result%3C%2FTD%3E%3CTD%20width%3D%2271%22%3ETest%20date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlan%3C%2FTD%3E%3CTD%3Enegative%3C%2FTD%3E%3CTD%3E01.01.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBred%3C%2FTD%3E%3CTD%3Enegative%3C%2FTD%3E%3CTD%3E01.01.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlan%3C%2FTD%3E%3CTD%3Epositive%3C%2FTD%3E%3CTD%3E01.02.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECindy%3C%2FTD%3E%3CTD%3Epositive%3C%2FTD%3E%3CTD%3E01.01.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBred%3C%2FTD%3E%3CTD%3Enegative%3C%2FTD%3E%3CTD%3E02.03.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlan%3C%2FTD%3E%3CTD%3Enegative%3C%2FTD%3E%3CTD%3E01.03.2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECindy%3C%2FTD%3E%3CTD%3Enot%20tested%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBred%3C%2FTD%3E%3CTD%3Epositive%3C%2FTD%3E%3CTD%3E05.03.2021%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eand%20the%20request%20is%20to%20get%20number%20of%20negative%20and%20positive%20people.%20The%20person%20is%20negative%20or%20positive%20based%20on%20his%20latest%20test.%20(so%20negative%3D1%20(Alan%20from%201.3.)%2C%20positive%3D1%20(Bred%20from%205.3.)%2C%20not%20tested%20%3D1%20(Cindy%20from%20%22not%20tested%22%20row)%3C%2FP%3E%3CP%3EThe%20table%20is%20used%20in%20a%20dashboard%20with%20slicer%2C%20so%20the%20result%20should%20also%20reflect%20the%20filtered%20table.%3C%2FP%3E%3CP%3ESee%20attachment%20with%20details.%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3EZdenek%20Moravec%3C%2FP%3E%3CP%3ECesky%20Krumlov%2C%20Czech%20Republic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2200921%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2202171%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20values%20with%20max%20date%20in%20filtered%20excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2202171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20assume%20you%20are%20on%20Excel%20365.%20With%20that%3C%2FP%3E%0A%3CP%3EI'd%20add%20Helper%20column%20to%20the%20table%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%5B%40%5BTest%20date%5D%5D%3D%22%22%2C99999%2C%5B%40%5BTest%20date%5D%5D)*AGGREGATE(3%2C5%2C%5B%40Name%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIt%20returns%20zero%20for%20hided%20by%20filter%20rows%2C%2099999%20if%20date%20is%20missed%20and%20Test%20date%20otherwise.%3C%2FP%3E%0A%3CP%3EWhen%20number%20of%20employees%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(%20FILTER(%20T_Data%5BName%5D%2C%20T_Data%5BHelper%5D)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20number%20of%20positive%20employees%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(%0A%20%20%20%20(MAXIFS(T_Data%5BHelper%5D%2CT_Data%5BName%5D%2CT_Data%5BName%5D%2CT_Data%5BHelper%5D%2C%22%26gt%3B0%22)%20%3D%0A%20%20%20%20%20%20IF(T_Data%5BHelper%5D%2CT_Data%5BHelper%5D%2C-1%20%20))*%0A%20%20%20%20%20(T_Data%5BTest%20Result%5D%3D%22positive%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESame%20for%20negative%20and%20not%20tested%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2203698%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20values%20with%20max%20date%20in%20filtered%20excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2203698%22%20slang%3D%22en-US%22%3EHello%20Sergei%3CBR%20%2F%3EThank%20You%20very%20much%2C%20it%20really%20works.%3CBR%20%2F%3EMay%20I%20ask%20You%20to%20explain%20MAXIFS%20%22criteria%20range%3Dname%2C%20criteria%3Dname%22%3CBR%20%2F%3EAnd%20IF%20helper%2C%20then%20helper%2C%20else%20%3D-1%3CBR%20%2F%3EThank%20You%3CBR%20%2F%3EZdenek%3C%2FLINGO-BODY%3E
Contributor

Dear colleagues

I still record Covid tests in an excel table

NameTest ResultTest date
Alannegative01.01.2021
Brednegative01.01.2021
Alanpositive01.02.2021
Cindypositive01.01.2021
Brednegative02.03.2021
Alannegative01.03.2021
Cindynot tested 
Bredpositive05.03.2021

and the request is to get number of negative and positive people. The person is negative or positive based on his latest test. (so negative=1 (Alan from 1.3.), positive=1 (Bred from 5.3.), not tested =1 (Cindy from "not tested" row)

The table is used in a dashboard with slicer, so the result should also reflect the filtered table.

See attachment with details.

Thank You

Zdenek Moravec

Cesky Krumlov, Czech Republic

7 Replies

@Zdenek_Moravec 

I assume you are on Excel 365. With that

I'd add Helper column to the table with formula

 

=IF([@[Test date]]="",99999,[@[Test date]])*AGGREGATE(3,5,[@Name])

 

It returns zero for hided by filter rows, 99999 if date is missed and Test date otherwise.

When number of employees

 

=COUNTA(UNIQUE( FILTER( T_Data[Name], T_Data[Helper])))

 

and number of positive employees

 

=SUM(
    (MAXIFS(T_Data[Helper],T_Data[Name],T_Data[Name],T_Data[Helper],">0") =
      IF(T_Data[Helper],T_Data[Helper],-1  ))*
     (T_Data[Test Result]="positive")
)

 

Same for negative and not tested

Hello Sergei
Thank You very much, it really works.
May I ask You to explain MAXIFS "criteria range=name, criteria=name"
And IF helper, then helper, else =-1
Thank You
Zdenek

@Zdenek_Moravec 

Hi Zdenek,

 

Let assume we filtered the table on ACC

image.png

and let split our formula on parts

image.png

Here

=MAXIFS(T_Data[Helper],T_Data[Name],T_Data[Name])

returns max date from helper against each name in the table. For hided rows helper is zero, otherwise max of available dates for the name in row. Without that we will have only one max value for all names.

Next is helper column which we would like to compare with previous result and return 1 if dates are equal (i.e. date in helper is equal to max date for the name), otherwise zero. 

But if simply compare these two arrays all hided rows also return 1 (0=0), thus we use IF() to change helper for hided rows on -1.

 

In addition, the part T_Data[Name],T_Data[Helper],">0" in formula is not needed, it is from my initial variant and I forgot to remove. Finally formula could be

=SUM(
    (MAXIFS(T_Data[Helper],T_Data[Name],T_Data[Name]) =
      IF(T_Data[Helper],T_Data[Helper],-1  ))*
     (T_Data[Test Result]="positive")
)

Hello @Sergei Baklan 

I have reached my Excel limit, I cannot get this mega formula. One new thing for me is, that a reference to a table returns 0 for the filtered rows. The rest I must probably repeat more and more times in a free time.

Anyway, the formulas add up the max's of filtered table perfectly and I have implemented it.

Last thing - I need to count the total number of positive and negative tests.

I tried to COUNTA FILTERed list with the criteria:

=COUNTA(FILTER(T_Data[Test Result];(T_Data[Helper])*(T_Data[Test Result]="positive")))

The problem is, when FILTER returns empty array, the result of COUNTA is 1, not 0 or error (see attachment)

Is there a solution with the new dynamic formulas or I must use the old formula from google?

Thank You very much

Zdenek Moravec

 

best response confirmed by Zdenek_Moravec (Contributor)
Solution

@Zdenek_Moravec 

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

Hello@Sergei Baklan 

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_Moravec 

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.