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.

 

[etaf]

For 1.
I have used
=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")

NOT the $ fixing the cell at the first entry and also then relative for the first entry so as the formula copies down, it only counts the row its on and above - NOT below
This assumes the dates are sorted in the order you attended otherwise different solution needed maybe with a MIN(DATE)

and 2.
just a
=COUNTIFS($C$4:C5,C5,$E$4:E5,E5)
This assumes the dates are sorted in the order you attended otherwise different solution needed maybe with a MIN(DATE)

i have not used any dates, as i assume each entry is unique occurrence and you would not duplicate, and the spreadsheet is sorted/ordered by date

parkrun - Volunteering Example (Slimmed).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																																														1	1
8	Riverfront parkrun		Photographer	06/01/2018																																														1	1
9	Newport parkrun		Barcode Scanning	13/01/2018																																														1	1
10	Newport parkrun		Finish Token Support	20/01/2018																																														1	1
11	Newport parkrun		Number Checker	27/01/2018																																														1	1
12	Riverfront parkrun		Barcode Scanning	03/02/2018																																														1	1
13	Riverfront parkrun		Number Checker	17/02/2018																																														1	1
14	Riverfront parkrun		Number Checker	24/02/2018																																															2
15	Newport parkrun		Number Checker	24/03/2018																																															2
16	Newport parkrun		Number Checker	12/05/2018																																															3
17	Penallta parkrun		Number Checker	26/05/2018																																														1	1
18	Penallta parkrun		Finish Token Support	02/06/2018																																														1	1
19	Newport parkrun		Number Checker	09/06/2018																																															4
20	Penallta parkrun		Event Day Course Check	23/06/2018																																														1	1
21	Penallta parkrun		Finish Token Support	30/06/2018																																															2
22	Bryn Bach parkrun		Barcode Scanning	07/07/2018																																														1	1
23	Newport parkrun		Number Checker	14/07/2018																																															5
24	Riverfront parkrun		Finish Token Support	21/07/2018																																															2
25	Severn Bridge parkrun		Pre-Event Setup	11/08/2018																																														1	1
26	Cwmbran parkrun		Photographer	18/08/2018																																														1	1
27	Pontypool parkrun		Barcode Scanning	25/08/2018																																														1	1
28	Newport parkrun		Marshal	08/09/2018																																															3
29	Pontypridd parkrun		Marshal	29/09/2018																																														1	1
30	Pontypridd parkrun		Car Park Marshal	20/10/2018																																														1	1
All Completed VS

Cell Formulas
Range	Formula
AZ4:AZ30	AZ4	=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")
BA4:BA30	BA4	=COUNTIFS($C$4:C4,C4,$E$4:E4,E4)

 

[ollyhughes1982]

For 1.
I have used
=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")

and 2.
just a
=COUNTIFS($C$4:C5,C5,$E$4:E5,E5)

i have not used any dates, as i assume each entry is unique occurance and you would not duplicate

parkrun - Volunteering Example (Slimmed).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																																														1	1
8	Riverfront parkrun		Photographer	06/01/2018																																														1	1
9	Newport parkrun		Barcode Scanning	13/01/2018																																														1	1
10	Newport parkrun		Finish Token Support	20/01/2018																																														1	1
11	Newport parkrun		Number Checker	27/01/2018																																														1	1
12	Riverfront parkrun		Barcode Scanning	03/02/2018																																														1	1
13	Riverfront parkrun		Number Checker	17/02/2018																																														1	1
14	Riverfront parkrun		Number Checker	24/02/2018																																															2
15	Newport parkrun		Number Checker	24/03/2018																																															2
16	Newport parkrun		Number Checker	12/05/2018																																															3
17	Penallta parkrun		Number Checker	26/05/2018																																														1	1
18	Penallta parkrun		Finish Token Support	02/06/2018																																														1	1
19	Newport parkrun		Number Checker	09/06/2018																																															4
20	Penallta parkrun		Event Day Course Check	23/06/2018																																														1	1
21	Penallta parkrun		Finish Token Support	30/06/2018																																															2
22	Bryn Bach parkrun		Barcode Scanning	07/07/2018																																														1	1
23	Newport parkrun		Number Checker	14/07/2018																																															5
24	Riverfront parkrun		Finish Token Support	21/07/2018																																															2
25	Severn Bridge parkrun		Pre-Event Setup	11/08/2018																																														1	1
26	Cwmbran parkrun		Photographer	18/08/2018																																														1	1
27	Pontypool parkrun		Barcode Scanning	25/08/2018																																														1	1
28	Newport parkrun		Marshal	08/09/2018																																															3
29	Pontypridd parkrun		Marshal	29/09/2018																																														1	1
30	Pontypridd parkrun		Car Park Marshal	20/10/2018																																														1	1
All Completed VS

Cell Formulas
Range	Formula
AZ4:AZ30	AZ4	=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")
BA4:BA30	BA4	=COUNTIFS($C$4:C4,C4,$E$4:E4,E4)

That's brilliant. Thanks!

 

[etaf]

you are welcome, I added a note re sort order and dates

 

[ollyhughes1982]

Yes, they are always entered / sorted by date. When I've added into my version I get some zeroes at the bottom, is there a way for the cells to appear blank when there is no entry in columns C / E? Also, I think the Merthyr column BA78 should be 1? As this is the first time First Timers Briefing is completed at Merthyr?

Thanks again.

 

[ollyhughes1982]

I don't think it's actually working in the way I wanted. I think it's my rubbish explanation. I will try and manually populate so you can see what I am after.

 

[etaf]

Crossed Posted, i await the update

Mine shows as 1

What formula do you have in BA , does it start with the correct rows ?

Do all the other values show up correctly

to stop blank cells showing a count of zero
then
try
=IF(C4="","",COUNTIFS($C$4:C4,C4,$E$4:E4,E4))

I have added to Dropbox, NOTE only stays on for a week, so you can see the whole example - XL2BB to many cells to show

Dropbox - File Deleted - Simplify your life

www.dropbox.com

BUT a small sample here as well for forum future searches

parkrun - Volunteering Example (Slimmed) - ETAF.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																																														1	1
8	Riverfront parkrun		Photographer	06/01/2018																																														1	1
9	Newport parkrun		Barcode Scanning	13/01/2018																																														1	1
10	Newport parkrun		Finish Token Support	20/01/2018																																														1	1
11	Newport parkrun		Number Checker	27/01/2018																																														1	1
12	Riverfront parkrun		Barcode Scanning	03/02/2018																																														1	1
13	Riverfront parkrun		Number Checker	17/02/2018																																														1	1
14	Riverfront parkrun		Number Checker	24/02/2018																																															2
15	Newport parkrun		Number Checker	24/03/2018																																															2
16	Newport parkrun		Number Checker	12/05/2018																																															3
17	Penallta parkrun		Number Checker	26/05/2018																																														1	1
All Completed VS

Cell Formulas
Range	Formula
AZ4:AZ17	AZ4	=IF(COUNTIFS($C$4:C4,C4,$E$4:E4,E4)=1,1,"")
BA4:BA17	BA4	=IF(C4="","",COUNTIFS($C$4:C4,C4,$E$4:E4,E4))

 

[ollyhughes1982]

Hi. I have just uploaded a version with the cells entered manually, to illustrate what I'm after. If you look at the spreadsheet in a filtered view, with venues selected where i have done quite a few (Newport, Penallta, Riverfront), it illustrates it best. I've attached screenshot examples for Penallta and Riverfront. You can see that whilst there won't always be a number in AZ, there will always be one in BA, as this is the count for AZ.

Link for this manually populated version is here: parkrun - Volunteering Example (Slimmed)_v2.xlsx

Thanks,

Olly.

 

[etaf]

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

 

[ollyhughes1982]

Brilliant, thank you. I will have a look now