Broken Count formula

[Notters]

Hi there, please tsee the attached the formula in D4 and D9 have stopped working and i cant for the life of me work out how to fix them.

They should return the number of unique accurances of a number (account number in this case) that appear in a column based on date perameters

Can somebidy please help.

Thanks

[benishiryo]

hi Notters, your ranges seemed to have been messed up. D4 is looking at different ranges. D9 is not looking up till row 1018 in "Datadrop". i changed a couple of things in your file:

1. you should not lookup or colour a whole column of 1 million over rows. I converted your data into a Table. you can do that by selecting your data & press CTRL + T. the next row filled will be coloured according to the previous cell & also captured in your formula so that you dont have to keep adjusting. you can see that the file is significantly smaller & faster

2. your formula in B5 & B6 is excluding the date of column E as you input as ">E5". i assume it should be ">=E5"?