Creating an Excel formula

Copper Contributor

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

@Pieter23 

 

is this what you want.

@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'.

@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.

Screenshot 2021-11-24 at 11.39.20.png

 

If you are new to PQ, check out the link below. It's a good starting point to learn more about it.

https://exceloffthegrid.com/power-query-introduction/ 

Thank you @Riny_van_Eekelen!! Makes life easier. Now I only have to learn how to use Power Query...
@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?

@Pieter23 

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

image.png

@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.

@Pieter23 

Like this ?

image.png

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