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

On a third sheet I have a table with the regions and I want to count how much Unique numbers had both a startsession and presentation for methodB.

Example, if the screenshot is the source file. The new table must show:

Noord holland      1

Overijssel              1

Gelderland            0

Noord brabant      0

...

...

8 Replies

Re: Creating an Excel formula

is this what you want.

Re: Creating an Excel formula

@arnel_gp Not exactly. The result in sheet 3 should only count if a unique number has had both a presentation AND a startsession. So in my example 'Noord holland' and 'Overijssel' should return 1 and 'Zuid holland' should return 0, since the uniqe number only had a 'Presentation'.

Re: Creating an Excel formula

@Pieter23 Let me start by thanking @arnel_gp for taking the time to create a workbook that replicates your data. It enabled me to, fairly easily, create the desired output with Power Query as you can see in the attached file. It may need to be optimized to fit your real life data, but it demonstrates what you can achieve without complex formulae. Re: Creating an Excel formula

Thank you @Riny_van_Eekelen!! Makes life easier. Now I only have to learn how to use Power Query...

Re: Creating an Excel formula

@Riny_van_Eekelen watched couple of youtube tutorials but still did'nt find out how to get the result I need. Can you help out?

Re: Creating an Excel formula

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 Re: Creating an Excel formula

@Sergei Baklan, 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.

Re: Creating an Excel formula

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