Forum Discussion

Pieter23's avatar
Pieter23
Copper Contributor
Nov 24, 2021

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

...

...

excel
Formulas and Functions

8 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 24, 2021

    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.

     

    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/ 

    • Pieter23's avatar
      Pieter23
      Copper Contributor
      Nov 25, 2021
      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?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 25, 2021

        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

    • Pieter23's avatar
      Pieter23
      Copper Contributor
      Nov 24, 2021
      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'.

Resources