Can you copy array formula down without having to hit Ctrl+Shift+Enter?

1
votes

I tried a fix like under "excel array formula: not have to 'ctrl-shift-enter'?" Here's my formula:

=(0*0)+SUM(IF(A10=Bundle!$B$2:$B$58233,1/(COUNTIFS(Bundle!$B$2:$B$58233,A10,Bundle!$N$2:$N$58233,Bundle!$N$2:$N$58233)),0))

The "before the fix" formula works, but there are a lot of rows, and it would take a long time to Ctrl+Shift+Enter all 2624 times. I added the code as suggested and the "0*0" like above, but it didn't work. Do I have to do something to activate the code?

This and several other posts online suggest that array formulas will just fill-down, but that doesn't work either.

Thanks, John

1
arraysexcel
You should be able to just drag the formula down, like any other. What happens when you do so? Also, where was it suggested to do (0*0)? I'm not saying it's incorrect, only that it looks like you're getting some tips from somewhere and I'm just curious as to where. (I.e. to understand what you mean "before the fix"). Also, does this question on SO help? – BruceWayne
You are think of a Google-Docs sheet that autopopulates cells below the cell where an array-type function is input. – user4039065
The question you refer to is where I got the (0*0). It has this code to go with it: Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim cell As Range For Each cell In Target If Target.Cells.Count = 1 Then If InStr(1, Target.Formula, "(0*0)+", vbTextCompare) And _ Target.HasArray = False Then Target.FormulaArray = Target.Formula End If End If Next cell End Sub – John Nesbit
You might also want to look into an alternate solution that doesn't fill 2625 rows with an array formula that references 58232 rows of data. – user4039065
This is the only solution I've found other than manually counting each location (Bundle Column N) within each customer account (Bundle Column B). It's a $10 mil question for my client, so I'd like to figure it out. I'm going to try pulling just the columns I need out and then subtotaling. It might work without the other columns present (it didn't before). – John Nesbit

1 Answers

2
votes

If the array formula is in cell B2, and you want to copy and paste the formula to cells B3:B10, you can ONLY paste the formulas to cells B3:B10. You can't include cell B2 when you paste the formulas or you will get a "you cannot change part of an array" error message.