Excel 365 not responding

[Pauljj]

Any thoughts on this query please

I am using the latest version of excel of have a report which contains 340,210 rows of data. Columns A to L are populated. With no formulas or formatting this is already a large document, however on a PC which has an 8 core processor I added a formula to M1. I tried to copy this down to row 15,000 and the workbook crashed. So I decided to switch the formula calculations to manual. I then tried to drop the formula to the end of the report.

The workbook has now not been responding for 3 hours

I appreciate the file is largish but ultimately it's one column with formulas on a spreadsheet which is only using a third of it's total rows....why should it find this so hard ?

 

[AliGW]

How large is the workbook? Where is it stored? Networked drive, local hard drive, cloud, etc.

 

[Pauljj]

It's 28mb in size. It crashed again after 4 hours

it seems to struggle me dragging the formula down by more then 1,000 rows. As you could imagine, doing this over 300,000 could end up taking a very long time......and this is with the calculations set to manual

The formula is

=IF(AND(COUNTIF($B$3:B3,B3,$F$3:F3,"A")=1,F3="A"),"Yes","")

Not particularly complex....

 

[AlKey]

I think you should replace your COUNTIF bit with COUTNIFS

COUNTIFS($B$3:B3,B3,$F$3:F3,"A")

 

[Pauljj]

Sorry typo on my adding to this forum, I have it as 'COUNTIFS'