My formula runs slow when changing it.

I tried to change this formula from 2000 to 49999 lines to check because my report increased but it is taking for ever for excel to change these 6 values. Also when i did this it stopped counting all cells. O29 and P29 are date references FOR (029=12/01/16 P29=12/31/16). A column just equals 8568 and the rest counts the cells in I column if it is a unique number to the ones it found to be true. Did I screw up somewhere

=SUMPRODUCT(--(JOET!$H$1:$H$49999>=O29),--(JOET!$H$1:$H$49999<=P29),--(JOET!$A$1:$A$49999=8568),--IF(JOET!$I$1:$I$49999<>0,IF(1/COUNTIF(JOET!$I$1:$I$49999,JOET!$I$1:$I$49999)=1,1,0)))

EDIT: Here is an example of what should make the count 1.
---A-----B----------C----------D-----------E---------F-----------G-----------H----------I---
8568---12/28/16---$16.44---$16.44-----$221.44---$205.00-----$205.00-----02/29/16--5628399

It does not supprise me with counting unique distinct value and 3 criteria comparision for each row in 50000 rows!!!

Maybe VBA could help.

Originally Posted by Dark0Prince

=SUMPRODUCT(--(JOET!$H$1:$H$49999>=O29),--(JOET!$H$1:$H$49999<=P29),--(JOET!$A$1:$A$49999=8568),--IF(JOET!$I$1:$I$49999<>0,IF(1/COUNTIF(JOET!$I$1:$I$49999,JOET!$I$1:$I$49999)=1,1,0)))

What is your formula supposed to be doing?

if you have leading zeros, then that is text, not numeric. Hard to say from your formula which part looks at that, but if it is 1 of those cell refs, then that needs to be text as well, and include all leading zeros

I couldn't get your formula to do anything but throw errors until I changed it to read this way

Formula:

Please Login or Register  to view this content.