Forum Discussion
Creating an Excel formula
There are 2 sheets with each 4 columns. Type: Startsession or Presentation Method: Method A, B or C Number: Unique identification number which can occur multiple times Region: Region can occur mu...
As variant
= LET(
u, SORT( UNIQUE( Table1[Region]) ),
cnt, --( u = TRANSPOSE( Table1[Region] ) ),
pr, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "Presentation" ),
ss, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "StartSession" ),
CHOOSE( {1,2},
u,
MMULT( cnt, pr ) * MMULT( cnt, ss) )
)which gives
SergeiBaklan, thank you for your response. The only criteria missing in the formula is the unique number. Based on the unique number a region must count +1 if the number has both had a StartSession and Presentation for methodB.
Like this ?
= LET( u, SORT( UNIQUE( Table1[Region]) ), cnt, --( u = TRANSPOSE( Table1[Region] ) ), pr, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "Presentation" ), ss, --( Table1[Method] = "methodB" ) * ( Table1[Type] = "StartSession" ), CHOOSE( {1,2}, u, MMULT( cnt, pr + ss ) ) )