AGGREGATE function not working as expected

[ollyhughes1982]

Hi,

I have the below table and the following formula:

=IF('All Completed Runs'!C4="","",IFERROR(AGGREGATE(14,6,'All Completed Runs - LETTER-I.'!C4#/(COUNTIF('All Completed Runs - LETTER-I.'!C4#,">="&'All Completed Runs - LETTER-I.'!C4#)>='All Completed Runs - LETTER-I.'!C4#),1),1))

This works for me in another almost identical worksheet where I have parkrun event names in column B, rather than just their first letter. But this doesn’t seem to be working when I have just the letters in column B. This is the only difference between the worksheets, so cannot understand why the same formula (just with the reference worksheet name changed) doesn’t work.

The other (working) table looks like this:

The above table uses the following formula (which works):

=IF('All Completed Runs'!C4="","",IFERROR(AGGREGATE(14,6,'All Completed Runs - p-I.'!C4#/(COUNTIF('All Completed Runs - p-I.'!C4#,">="&'All Completed Runs - p-I.'!C4#)>='All Completed Runs - p-I.'!C4#),1),1))

The result I am getting is 1. However, the result I should be getting is 8, as there 8 or more letters that I have done 8 or more times. In my case I have done the letters A B C L M P S T W all 8 or more times. I can’t understand why I am getting a result of 1??

Thanks in advance!

Olly.

 

[ollyhughes1982]

Apologies, this has now been resolved by using

=IF(B4="","",COUNTIF('All Completed Runs'!AT:AT,B4#))

in C4 of column C. Not sure why this works, but using this formula then makes the original calculation

=IF('All Completed Runs'!C4="","",IFERROR(AGGREGATE(14,6,'All Completed Runs - LETTER-I.'!C4#/(COUNTIF('All Completed Runs - LETTER-I.'!C4#,">="&'All Completed Runs - LETTER-I.'!C4#)>='All Completed Runs - LETTER-I.'!C4#),1),1))

work. Thanks.