Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

SumProduct

0

On my "Aged List" workbook I have a formula in column G "=IF(SUMPRODUCT(--(Table7[Policy Number]=$A2),--(Table7[Work Type]=$B2)),"Yes","No")"

Where I basically need to match combinations of Column A & Column B on "Source" tab to the "ITS" tab on column A & B)

The formula works great. But i have an issue. The calculation can take up to 20-30 minutes at times. And when I open it back up after closing it locks all my sheets up for twice as long. Is there a more efficent way to run this across 40K plus lines?

Answer
Discuss

Answers

0
Selected Answer

I tried the formula below which seems to run much faster.

=IF(COUNTIFS(Table7[Policy Number],$A2,Table7[Work Type],$B2),"Yes","No")

Still, it took almost half a minute on the sample sheet I have which is too long. It might take longer in your own project. I would seek to avoid that my constructing the work flow in such a way that only one calculation needs to be done at any one time, e.g. when a data set is added.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login