Need a Faster Formula

[dguillen]

Hey All,

I currently have a formula that works very well except for the fact that it works SLOW. I've read that the COUNTIFS statement works really slow since it has to scan each cell in the Criteria Range even if it finds the Criteria. I've tried reducing the criteria range to stop at 300,000 but it still shows "Calculating (4 processor(s)) xx%. Can this formula be re-written to still provide the same end results? (place an "X" when it finds the criteria in the "Completed Training" sheet)

=IF(COUNTIFS('Completed Training'!$C$1:$C$300000,LOOKUP(C$1,C$1),'Completed Training'!$I$1:$I$300000,"SFUGN020 *") >0, "X", "")

Any help will be GREATLY appreciated.

 

[Aladin Akyurek]

What does that LOOKUP bit mean here?

 

[dguillen]

Aladin--- Not sure what you're asking.... do you need anything else from me to help with this post?

 

[dguillen]

sorry..... i see what you mean.... LOOKUP(C$1,C$1) is referring to an employee name that runs across row 1.. C1, D1, E1, etc...

 

[jtakw]

sorry..... i see what you mean.... LOOKUP(C$1,C$1) is referring to an employee name that runs across row 1.. C1, D1, E1, etc...

Hi,

I don't see the reason for the LOOKUP function, please read: https://support.office.com/en-us/article/LOOKUP-function-446d94af-663b-451d-8251-369d5e3864cb

Why not just:

=IF(COUNTIFS('Completed Training'!$C$1:$C$300000,C1,'Completed Training'!$I$1:$I$300000,"SFUGN020 *") >0, "X", "")
if you're copying this formula down column, and referencing C1, C2, C3, etc.

or

=IF(COUNTIFS('Completed Training'!$C$1:$C$300000,C$1,'Completed Training'!$I$1:$I$300000,"SFUGN020 *") >0, "X", "")
if you're copying this formula across columns, and referencing C1, D1, E1, etc.

 

[dguillen]

I agree with you jtakw, This formula was first created a few years back with a few different revisions on the workbook. i just removed it and it works perfectly fine. Thanks.

 

[jtakw]

You're welcome. Glad you worked it out.