Short version of a formula?

[F4TMAN]

Hello, I'm looking for the possibility of shortening the formula i currently have its really slowing down my excel and takes a long time to load.

=IF(AND(Expense!C2='P&L Month'!$I$5,COUNTIFS(Expense!$C$2:Expense!C2,Expense!C2,Expense!$D$2:Expense!D2,Expense!D2)=1),"YES","")

keeping in mind that I'm going to drag the formula down to row 5000. Since I'm referencing different tab sheets i was thinking about using name manager not sure if that would help. Any ideas? if you need more information please let me know.

Thank you.

 

[jasonb75]

By using 2 IF's instead of AND you can bypass the slower COUNTIFS function when C2 is not a match for $I$5.

=IF(Expense!C2='P&L Month'!$I$5,IF(COUNTIFS(Expense!$C$2:Expense!C2,Expense!C2,Expense!$D$2:Expense!D2,Expense!D2)=1,"YES",""),"")

Other than that, you could try different functions in place of countifs to see if anything is quicker, but to be honest I don't think that using that formula dragged down 5000 rows should be causing too much of a problem.

I set up a test sheet with 5000 rows of random data and it takes 2 seconds to load with your original formula (which will include the time it takes for the test data to randomise).