UNIQUE(FILTER amendement required

[ollyhughes1982]

Hi,

Re the screenshot below, I have a small worksheet that calculates the V-Index for my parkrun volunteering. I have the following formulas in B4 and C4.

B4:
=IFERROR(UNIQUE(FILTER('All Completed VS'!C4:C2003,'All Completed VS'!F4:F2003<>"")),"")

C4:
=IF(B4="","",COUNTIF('All Completed VS'!C:C,B4#))

This previously was thought to be working. However, I now realise that rather than every unique stint (i.e. each row) needing to be included, I should only be including each unique date (day). This is because multiple stints (roles) can be completed on each day.

The values in column C should be:
C4: 1 (I have voluneteered at Newport on 1 unique date)
C5: 2 (I have voluneteered at Newport juniors on 2 unique dates)
C6: 3 (I have voluneteered at Coed Cefn-pwll-du on 3 unique dates)

So, I need to use the same principle as in the original formulas, but for unique dates, rather than each unique row.

I have added some helper columns in AY, BG and BH which I think might help with this? Screenshots of main source spreadsheet, below:

Thanks in advance,

Olly.

 

[ollyhughes1982]

Here's a link to the file, if this makes it easier. It's quite a large file, so may take a minute to open.

One Drive: parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING.xlsx
DropBox: parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING.xlsx

Thanks again.

 

[ollyhughes1982]

To simplify. All I really want to do is count how many unique dates upon which I have completed volunteering at each event (venue). i.e. Only count the first line for each date, not the multiple lines. For example, only count 1 stint (day) for 29/08/2021, instead of 3. I think helper column BH should help with this?

Hopefully that explains it better?

Thanks again.

 

[ollyhughes1982]

I have now solved this by adding in a new column after BG that identifies the event (venue) that corresponds when there is a '1' in BG. Thanks.