Possible COUNTIF solution

[ollyhughes1982]

Hi,

I wonder if anyone might be able to help with some formulas, please. I have an existing table (columns A & B) that work perfectly, counting how many runs I have completed at each different parkrun venue. However, I would now like to some extra columns (C-G), but am not sure of how I calculate them. At the moment I have inserted the formulas that calculate what I want, but for all events, whereas I want them to calculate for the relevant event in that row.

So, what I essentially want to do is to keep the same calculation for each of the C-G columns, but with something added into the formula so that it is calculated only for that particular row's event.

At present, I have the following in columns C-G, which calculate correctly, but for all events:

• In A3: =IFERROR(UNIQUE(FILTER('All Completed Runs'!C3:C2002,'All Completed Runs'!E3:E2002<>"")),"")
• In B3: =IF(A3="","",COUNTIF('All Completed Runs'!C:C,A3#))
• In C3: =IF(A3="","",OFFSET(INDEX('All Completed Runs'!E3:E2002,MATCH(MAX('All Completed Runs'!E3:E2002),'All Completed Runs'!E3:E2002,FALSE)),0,21))
• In D3: =IF(A3="","",IFERROR(TEXT(SMALL('All Completed Runs'!Z3:Z2002,COUNTIF('All Completed Runs'!Z3:Z2002,0)+1),"HH:MM:SS"),""))
• In E3: =IF(A3="","",AVERAGE('All Completed Runs'!Z3:Z2002))
• In F3: =IF(A3="","",MAX('All Completed Runs'!Z3:Z2002))
• In G3: =IF(A3="","",IFERROR(MODE('All Completed Runs'!Z3:Z2002),"N/A - None / Multiple"))

Is it something really simple like just adding in a COUNTIF somewhere in each formula? I have tried a a few things, but without success.

Thanks in advance!

I have attached a link to the file via Google Drive:

parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING.xlsx

Also posted on Excel Forum: Possible COUNTIF solution

 

[jasonb75]

Changing COUNTIF to COUNTIFS might work for some of the formulas by comparing A3 to a column with event names in the 'All events' sheet.

Formulas with no explanation have little meaning.

A couple of small tables (expected results and a small sample of the source data to show about 20 rows of only the relevant columns) posted using XL2BB would give far more meaning to your post than a screen capture and an attached file (which many users may not be able to download anyway).

 

[ollyhughes1982]

Hi. I have cut it down as small as I can and it is still over 5mb in file size. I have included a link to it, below:

Example.xlsx

drive.google.com

What is Excel XL2BB?

I didn't originally do smaller version one because there are so many interconnects between worksheets etc. Bit of explanation as to the cells:

• C3: This should provide the time for the last time I completed the event in column A
• D3: This should provide the fastest (lowest) time out of all instances of the event in column A
• E3: This should provide the average (mean) time for all instances of the event in column A
• F3: This should provide the slowest (highest) time out of all instances of the event in column A
• G3: This should provide the most occurring (mode) time for the event in column A, or enter "N/A - None / Multiple" if this doesn't apply

Hopefully the explanation helps.

 

[jasonb75]

What is Excel XL2BB?

If you click the button on the reply toolbar, all will be revealed

I'm going to be offline for a bit, I'll check your thread later and look at the formulas for you then if it hasn't been resolved.

 

[ollyhughes1982]

If you click the button on the reply toolbar, all will be revealed

I'm going to be offline for a bit, I'll check your thread later and look at the formulas for you then if it hasn't been resolved.

Ah, I see, you have to download an add-in. I will give this a try

 

[ollyhughes1982]

MY parkruns - ALL COMPLETED RUNS - BY EVENT (VENUE)
Event (Venue)	# Completed	Latest Event (Venue) Finishing Time	Fastest Event (Venue) Finishing Time / Current Event (Venue) PB	Average Event (Venue) Finishing Time	Slowest Event (Venue) Finishing Time	Most Frequent Event (Venue) Finishing Time
Newport parkrun	2	0:28:07	00:24:41	0:26:22	0:28:07	N/A - None / Multiple
Ashton Court parkrun	1
Groe parkrun	1
Gloucester North parkrun	1

Source Sheet:

MY parkruns - ALL COMPLETED RUNS
Run #	Event (Venue) Run #	Event (Venue)	Country	Date Completed	Finishing Position #	# of Finishers	Best Finishing Position #?	Best Event (Venue) Finishing Position #?	Gender Finishing Position #	# of Gender Finishers	Best Gender Finishing Position #?	Best Event (Venue) Gender Finishing Position #?	PB?	Overall PB?	Event (Venue) PB?	PB Streak #	First Timer?	Assisted?	Age Group	Age Group Finishing Position #	# of Age Group Finishers	Best Age Group Finishing Position #?	Best Event (Venue) Age Group Finishing Position ?	Club	Time	Age Grade %	Best Age-Grade %?	Best Event (Venue) Age-Grade %?	Home Page Link	Course Page Link	Weekday

 

[Fluff]

For D, E & F have a look at minifs, maxifs & averageifs

 

[mamady]

For C, you can use the below formula

=INDEX('All Completed Runs'!$Z$3:$Z$2002,MATCH(A3&MAX(IF('All Completed Runs'!$C$3:$C$2002=A3,'All Completed Runs'!$E$3:$E$2002,0)),'All Completed Runs'!$C$3:$C$2002&'All Completed Runs'!$E$3:$E$2002,0))

 

[Fluff]

Or, using filter
=IF(A3="","",FILTER('All Completed Runs'!$Z$3:$Z$2002,('All Completed Runs'!$C$3:$C$2002=A3)*('All Completed Runs'!$E$3:$E$2002=MAXIFS('All Completed Runs'!$E$3:$E$2002,'All Completed Runs'!$C$3:$C$2002,A3))))

 

[ollyhughes1982]

Thanks all. I will try these when I get home later ?