Possibly countifs for two helper columns

[ollyhughes1982]

Hi all,

I have the ‘All Completed VS’ worksheet (screenshot below and link attached). The worksheet shows all of the parkruns at which I have volunteered and the volunteering role that I completed at each. I am looking to do some calculations on this worksheet, but cannot figure out how to calculate the two helper columns (highlighted in orange) that I need.

The calculations I want to make are in the following two columns:

1. AZ ‘Unique Role # at this Unique Event (Venue)’
- I want this to number the column each time that a new role is completed at each particular unique event (venue). e.g In my example, there should be a ‘1’ in AZ4 (first Marshal role at Newport), a blank in AZ5, a 1 in AZ6 (first Finish Token Support role at Riverfront), a 1 in AZ7 (first Pre-Event Setup role at Newport), a 1 in AZ8 (first Photographer role at Riverfront) and so on…


2. BA ‘Unique Role Stint # at this Unique Event (Venue)’
- I want this to number the column each time that a new stint for each particular unique role is completed at each particular unique event (venue). e.g In my example, there should be a ‘1’ in AZ4 (first Marshal role at Newport), a ‘2’ in AZ5 (second Marshal role at Newport), a 1 in AZ6 (first Finish Token Support role at Riverfront), a 1 in AZ7 (first Pre-Event Setup role at Newport), a 1 in AZ8 (first Photographer role at Riverfront) and so on…

Link: parkrun - Volunteering Example (Slimmed).xlsx

Thanks in advance,

Olly.

 

[ollyhughes1982]

the linked file is not the same as the illustrations

i just thought you wanted a 1 or blank in column AZ - the linked file has 1,2,3,4,5

parkrun - Volunteering Example (Slimmed)_v2.xlsx
	C		E	F																																														AZ	BA
3	Event (Venue)		Role	Date Completed																																														Unique Role # at this Unique Event (Venue)	Unique Role Stint # at this Unique Event (Venue)
4	Newport parkrun		Marshal	25/12/2017																																														1	1
5	Newport parkrun		Marshal	30/12/2017																																															2
6	Riverfront parkrun		Finish Token Support	01/01/2018																																														1	1
7	Newport parkrun		Pre-Event Setup	01/01/2018																																														2	1
8	Riverfront parkrun		Photographer	06/01/2018																																														1	1
9	Newport parkrun		Barcode Scanning	13/01/2018																																														3	1
10	Newport parkrun		Finish Token Support	20/01/2018																																														4	1
11	Newport parkrun		Number Checker	27/01/2018																																														5	1
12	Riverfront parkrun		Barcode Scanning	03/02/2018																																														1	1
13	Riverfront parkrun		Number Checker	17/02/2018																																														1	1
All Completed VS

I have copied your manual entries into the 2 columns marked manual
added the formulas as before in first 2 columns
then added a comparison of the calc and manual
and filtered for Riverfront parkrun

Event (Venue)	Role	Date Completed	Unique Role # at this Unique Event (Venue)	Unique Role Stint # at this Unique Event (Venue)	Manual	Manual	Compare	Compare
Riverfront parkrun	Finish Token Support	01/01/2018	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Photographer	06/01/2018	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Barcode Scanning	03/02/2018	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Number Checker	17/02/2018	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Number Checker	24/02/2018		2		2	TRUE	TRUE
Riverfront parkrun	Finish Token Support	21/07/2018		2		2	TRUE	TRUE
Riverfront parkrun	Marshal	25/12/2018	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Marshal	01/01/2019		2		2	TRUE	TRUE
Riverfront parkrun	Event Day Course Check	19/01/2019	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Event Day Course Check	27/04/2019		2		2	TRUE	TRUE
Riverfront parkrun	Run Report Writer	28/09/2019	1	1	1	1	TRUE	TRUE
Riverfront parkrun	Tail Walker	16/11/2019	1	1	1	1	TRUE	TRUE

How do I access the file, please? I don't have dropbox

 

[ollyhughes1982]

No worries, I have now signed up to dropbox free version and have downloaded the file. It seems to be working perfectly. Thank you for taking the time to look at this for me.

 

[etaf]

ok, so I did nothing to the formulas
glad its all working now as you needed

 

[ollyhughes1982]

ok, so I did nothing to the formulas
glad its all working now as you needed

Thanks again.

These two functions were the first part of what I think will help solve the problems that I identified in my previous message board post. Do you think that with these two helper columns I will now be able to solve it? I was thinking these two helper columns would be the key to being able solve it, so I thought it might be better to break it down into this smaller post first. My previous post is here: Helper columns and perhaps unique(filter functions required

I have now added in your two helper columns to that file (it’s the same file you worked on, but with all of the hidden worksheets / cells unhidden).

Thanks again,

Olly.

 

[ollyhughes1982]

I think I've actually confused myself on this one. The BA column is working great, but the the AZ column is actually only showing 1s, so isn't quite right. For Riverfront for example, the AZ column should appear as per the image below.

So, column AZ should show how many different unique roles have been completed at that venue and then BA shows the amount of each of these roles that has been completed at each venue. Column BA is working correctly.

Thanks. Apologies, it's quite complicated and previously confused myself!

Link: parkrun - Volunteering Example.xlsx

Thanks again.

 

[ollyhughes1982]

I am still struggling to get column AZ to calculate, if anyone might have any ideas. Thanks.

 

[etaf]

As a free forum, sometimes it may take a day or 2 to get answers, as volunteers here
for example - yesterday I had an Eye scan a photo - so avoided screens, today I'm out for rest of today till evening , and then thur out all day and friday out till evening - so maybe now saturday before i can followup on a reply

A quick solution however its to use a helper column
But i'm sure there maybe a way to have just one formula using a MAXIFS()
But as i say not got time right now to get into
Sorry if this is urgent

i have kept the original formula i suggested in Column AZ and now using as helper column
in column BA , which is the new column added for AZ I have put
=IF(AZ4="","",SUMIF($C$4:C4,C4,$AZ$4:AZ4))
Link to dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

parkrun - Volunteering Example (1).xlsx
	A	B	C		E	F																																														AZ	BA	BB	BC
3	Stint #	Event (Venue) Run #	Event (Venue)		Role	Date Completed																																														Unique Role # at this Unique Event (Venue)		Unique Role Stint # at Unique Event (Venue)	Manual
4	1	356	Newport parkrun		Marshal	25/12/2017																																														1	1	1	1
5	2	357	Newport parkrun		Marshal	30/12/2017																																																2
6	3	51	Riverfront parkrun		Finish Token Support	01/01/2018																																														1	1	1	1
7	4	358	Newport parkrun		Pre-Event Setup	01/01/2018																																														1	2	1	2
8	5	52	Riverfront parkrun		Photographer	06/01/2018																																														1	2	1	2
9	6	359	Newport parkrun		Barcode Scanning	13/01/2018																																														1	3	1	3
10	7	360	Newport parkrun		Finish Token Support	20/01/2018																																														1	4	1	4
11	8	361	Newport parkrun		Number Checker	27/01/2018																																														1	5	1	5
12	9	56	Riverfront parkrun		Barcode Scanning	03/02/2018																																														1	3	1	3
13	10	58	Riverfront parkrun		Number Checker	17/02/2018																																														1	4	1	4
14	11	59	Riverfront parkrun		Number Checker	24/02/2018																																																2
15	12	368	Newport parkrun		Number Checker	24/03/2018																																																2
16	13	375	Newport parkrun		Number Checker	12/05/2018																																																3
17	14	148	Penallta parkrun		Number Checker	26/05/2018																																														1	1	1	1
18	15	149	Penallta parkrun		Finish Token Support	02/06/2018																																														1	2	1	2
All Completed VS

Cell Formulas
Range	Formula
AZ4:AZ18	AZ4	=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")
BA4:BA18	BA4	=IF(AZ4="","",SUMIF($C$4:C4,C4,$AZ$4:AZ4))
BB4:BB18	BB4	=IF(C4<>"",IF(C4="","",COUNTIFS($C$4:C4,C4,$E$4:E4,E4)),"")
A4	A4	=IF(C4<>"",1,"")
A5:A18	A5	=IF(C5<>"",A4+1,"")

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ollyhughes1982]

Yes, that's no problem. Thanks so much for your help. In essence then the AZ column now identifies if it is a unique role for that event and then BA assigns a number to it? Thanks.

 

[ollyhughes1982]

That's working perfectly now. Thanks again.

 

[etaf]

Thanks so much for your help. In essence then the AZ column now identifies if it is a unique role for that event and then BA assigns a number to it? Thanks.

Yep, thats what it does, Unique role at event, then a count of roles at any event. I may come up with an all-in-1 formula if i get time, but if that solves and does what you need then i can leave as is