Possible countif or unique(filter function required

[ollyhughes1982]

Hi all,

I have the ‘All Completed VS’ worksheet (screenshot below and link attached).

The worksheet shows all of the volunteering roles that I have completed at various different parkruns. From this, I am looking to populate the ‘All Completed VS - VRE-I.’ worksheet (worksheet highlighted in red). i.e. List all of the different volunteer roles that I have completed in column B. Then, in column C, to show at how many unique events I have completed each role. I would like the data to be sorted descending, according the value in column C.

I have populated the cells with some manual data, just to show the kind of thing I am after (see screenshot, below).

Link to file is here: parkrun - Volunteering Example.xlsx

Thanks in advance,

Olly.

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER('All Completed VS'!E4:E500,'All Completed VS'!E4:E500<>"")),SORT(CHOOSE({1,2},u,SUMIFS('All Completed VS'!AZ4:AZ500,'All Completed VS'!E4:E500,u)),{2,1},{-1,1}))

 

[ollyhughes1982]

How about

=LET(u,UNIQUE(FILTER('All Completed VS'!E4:E500,'All Completed VS'!E4:E500<>"")),SORT(CHOOSE({1,2},u,SUMIFS('All Completed VS'!AZ4:AZ500,'All Completed VS'!E4:E500,u)),{2,1},{-1,1}))

Thanks Fluff, that worked great. Would you also know the solution to fill in the number in the yellow highlighted cell, please? I have manually inputted it, at present. I think the logic is to find the first row where the values in column A and C are equal. Thanks again.

 

[Fluff]

What would the result be if you had only been a Marshal 3 times & not 4, or been a Number Checker 4 times?

 

[ollyhughes1982]

What would the result be if you had only been a Marshal 3 times & not 4, or been a Number Checker 4 times?

Hi. It's not the total number of times, it's the total number of events at which they have completed. I will upload two different versions with your examples

 

[Fluff]

Can you please just answer the question.

 

[ollyhughes1982]

Two new versions, with the scenarios you requested:

parkrun - Volunteering Example_Fluff_MarshalAt3Events.xlsx

Microsoft Excel Workbook

1drv.ms

parkrun - Volunteering Example_Fluff_NumberCheckerAt4Events.xlsx

Microsoft Excel Workbook

1drv.ms

Thanks.

 

[Fluff]

See post#6

 

[ollyhughes1982]

See post#6

Reducing Marshal from 4 to 3 events, returns a changed result of of 3 (changed from 4).
Increasing Number checker to 3 from 4 events, returns the same result: 4 events.

 

[Fluff]

Ok, how about

=SUM(--(SEQUENCE(ROWS(B4#))<=INDEX(B4#,,2)))