SUMPRODUCT or IF / OR

[ollyhughes1982]

Hi all,

Please can someone help with my formula, below (in column D of the ‘All Completed Runs - AGFP Bingo’ worksheet):

=IFERROR(IF(B4="","",(SUMPRODUCT(--(RIGHT('All Completed Runs'!$U$4:$U$2003,2)=A4)))),"")

I am attempting to count the number of occasions where column U entries in the ‘All Completed Runs’ worksheet end in the values in column A of the ‘All Completed Runs - AGFP Bingo’ worksheet. It seems to work, apart from 01-09 - which have the leading 0. Whereas the entries in column U of ‘All Completed Runs’ worksheet do not. Is there a way to cater for this for the 01-09 ones? Perhaps some sort of if / or function?

The aim is to basically count all occasions in U of the ‘All Completed Runs’ worksheet where the last 2 digits correspond with column A entries in the ‘All Completed Runs - AGFP Bingo’ worksheet.

The formulas in B and C work correctly, but they are only finding the first occasion, rather than counting all occasions.

I have included some manual entries for what should be the count in column F (red incorrect, green correct).

I have included a link to the file, below:

AGFP Example.xlsx

Microsoft Excel Workbook

1drv.ms

Loading Google Sheets

The file is small and will open straight up.

Thanks in advance,

Olly.

 

[Fluff]

Maybe

=IFERROR(IF(B4="","",(SUMPRODUCT(--(TEXT(RIGHT('All Completed Runs'!$U$4:$U$2003,2),"00")=A4)))),"")

 

[ollyhughes1982]

Maybe

=IFERROR(IF(B4="","",(SUMPRODUCT(--(TEXT(RIGHT('All Completed Runs'!$U$4:$U$2003,2),"00")=A4)))),"")

Perfect! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.