Extra criteria in IF statement needed

[ollyhughes1982]

Hi all,

Re the screenshots above, I currently have the below formula in BJ11:

=IF(C11="","",IF(OR(C11="",F11=F10),"",COUNT(UNIQUE($F$4:F11))))

This is however missing something. I also need to add in the criteria that a new number needs to be added to the sequence if C11 is different to C10. This is because it is possible to volunteer at two separate parkruns on the same day. So, the numbering needs to account for situations where the date is the same, but the venue is different.

I would expect to see 6 in cell BJ11 and 7 in BJ12. This is because I volunteered later in the day on 29/08/2021 at a different venue (Cwmbran). For the first three roles I did that day, they were all at Newport junior parkrun.

N.B. An 'Occasion' signifies each unique venue that is volunteered at on a particular date. hence having Newport junior and Cwmbran on 29/08/2021 equals two different occasions (6th and 7th).

Thanks in advance,

Olly.

 

[ollyhughes1982]

Here is a OneDrive link to the file, if this helps:

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

Microsoft Excel Workbook

1drv.ms

The file is large, so can take a minute or so to open.

Thanks again!

 

[Candyman8019]

When you say your formula is "missing something" what do you mean by that?

Looking at the formula itself, it looks like you are testing for the same condition more than once and it could be simplified to the following for a unique count of values:
=IF(or(C11="",F11=F10),"",SUM(IF(FREQUENCY($F$4:F11,$F$4:F11)>0,1)))

 

[ollyhughes1982]

When you say your formula is "missing something" what do you mean by that?

Looking at the formula itself, it looks like you are testing for the same condition more than once and it could be simplified to the following for a unique count of values:
=IF(or(C11="",F11=F10),"",SUM(IF(FREQUENCY($F$4:F11,$F$4:F11)>0,1)))

Hi and thanks for responding. The bit I need to add in is as follows:

A new number needs to be added to the sequence if C11 is different to C10. This is because it is possible to volunteer at two separate parkruns on the same day. So, the numbering needs to account for situations where the date is the same, but the venue is different. In my example, I have 4 roles undertaken on 29/08/2021, but the first three were at Newport junior and the fourth was at Cwmbran, later in the day. So the 5 in BJ8 is correct, but then in stead of a blank in BJ11 there should be a 6 and then a 7 in BJ12. This is because each set of roles completed at a particular venue, on a particular day is called an ‘Occasion’. So, on the 28/09/2021, Newport junior was the first occasion and Cwmbran was the second occasion. Therefore, occasions 5 and 6 both took place on the same date. Three roles at Newport junior and one role at Cwmbran.

Thanks again.

 

[ollyhughes1982]

I have now solved this. I added a helper column (BJ) that combined the date (F) and first instance of event (BH) columns and then doing a sumproduct count on non-blank cells. I then did my numbering in column BK.

Thanks again.