Simplifying formulas to increase speed of excel workbook

[SaraB802]

So I have a large excel workbook, with lots of heavy formulas, which have now stalled the workbook totally. Below are the top 1 in my mind that is very bulky and I need to reduce the repetition in to make them and the workbook quick. I am not adverse to adding in extra cells or splitting code if that will work, but need to sort it out by Monday morning.

1)

=SUM(COUNTIFS('SUMMARY of AM ACCOUNTS'!E:E,'PSR TGTS'!A4,'SUMMARY of AM ACCOUNTS'!J:J,"DORMANT",'SUMMARY of AM ACCOUNTS'!R:R,"H",'SUMMARY of AM ACCOUNTS'!S:S,"H"),COUNTIFS('SUMMARY of AM ACCOUNTS'!F:F,'PSR TGTS'!A4,'SUMMARY of AM ACCOUNTS'!J:J,"DORMANT",'SUMMARY of AM ACCOUNTS'!R:R,"H",'SUMMARY of AM ACCOUNTS'!S:S,"H"),COUNTIFS('SUMMARY of AM ACCOUNTS'!G:G,'PSR TGTS'!A4,'SUMMARY of AM ACCOUNTS'!J:J,"DORMANT",'SUMMARY of AM ACCOUNTS'!R:R,"H",'SUMMARY of AM ACCOUNTS'!S:S,"H"),COUNTIFS('SUMMARY of AM ACCOUNTS'!H:H,'PSR TGTS'!A4,'SUMMARY of AM ACCOUNTS'!J:J,"DORMANT",'SUMMARY of AM ACCOUNTS'!R:R,"H",'SUMMARY of AM ACCOUNTS'!S:S,"H"))

I also have this one for "ACTIVE"

In lay terms the problem is because the source data, which I can't change has 4 possible columns for the Summary of AM Accounts E:E, F:F, G:G, and H:H which could contain the AMs number.

Can I countifs over the range E:H??

I had tried to limit the range from whole columns to specfic cell ranges, especially for the VLOOKUPs, but I have no way of knowing how many records will come in on the next update of information. Last count was over 17000 records

2) =IFERROR(INDEX('SUMMARY of AM ACCOUNTS'!$B$2:$B$18001,SMALL(IF('SUMMARY of AM ACCOUNTS'!$E$2:$E$18001=$B$1,ROW('SUMMARY of AM ACCOUNTS'!$E$2:$E$18001)),ROW(1:1)),1),"")

I have to use this one over the 4 column ranges in the above formula, but this time in each column it goes over 700 records, that I might need to extract in to each batch, so same formula repeated on 700 rows, 4 times (1 for each E:E,F:F etc)

All help would be much appreciated.

 

[drhatmrexcel]

As soon as you start you macro use this code to stop the screen updating.

Application.ScreenUpdating = False


And then the last thing before you end your macro turn screen updating back on with this code.

Application.ScreenUpdating = True


This should really speed things up especially if you have a really old computer.

 

[SaraB802]

Thanks, but I am a little confused. The formulas are in the cells, not in a macro, so how can I add the macro code you are suggesting and how would I set it to run