Inputting an Array formula over a number of cells simultaneously

[doug firr]

Lets say I have months of the year from Jan to Dec and for each a formula that requires an array formula.

The formula takes a long time to run ~ 45 mins but I can live with that.

When I type the formula under Jan I must CSE then wait 45 mins for it to run. As of typing this message I have dragged the formula over to Dec and it appears to be working but I'm still waiting for it to run.

My question then, is do I have to cse each cell individually or is there a way to highlight a range of cells and run them as arrays (CSE) in a oner?

 

[shg]

No, you can't.

Are you persuaded that there's no better formula that might take a small fraction of that time?

 

[doug firr]

I wish there was but I've asked the crowd already: http://www.mrexcel.com/forum/excel-questions/820823-sumproduct-unique-records-returned.html.... unless you know of another?

 

[shg]

I generated 100,000 random dates betwee 1/1/14 and 12/31/14, and next to that, 100,000 random numbers between 1 and 10,000, then converted all to values and sorted:

Row\Col	A​	B​
1​	Date​	Item​
2​	01/01/2014​	171​
3​	01/01/2014​	253​
4​	01/01/2014​	258​
5​	01/01/2014​	332​
6​	01/01/2014​	460​
7​	01/01/2014​	526​
8​	01/01/2014​	527​
9​	01/01/2014​	530​
10​	01/01/2014​	531​
11​	01/01/2014​	546​
12​	01/01/2014​	627​
13​	01/01/2014​	691​
14​	01/01/2014​	692​
15​	01/01/2014​	715​
16​	01/01/2014​	716​
17​	01/01/2014​	729​
18​	01/01/2014​	860​

The numbers in col B stand in for your email addresses.

Then I pivoted that, with dates grouped by month, and months duplicated down the column:

Row\Col	D​	E​
1​	Date	Item
2​	Jan	1​
3​	Jan	3​
4​	Jan	6​
5​	Jan	9​
6​	Jan	10​
7​	Jan	11​
8​	Jan	15​
9​	Jan	16​
10​	Jan	17​
11​	Jan	19​
12​	Jan	21​
13​	Jan	23​
14​	Jan	24​

The item column is intrinsically de-deduplicated. Then I created a simple table to count the pivot results.

Row\Col	G​	H​	I​
1​	Month​	Unique​
2​	Jan	5703​	H2: =COUNTIF(D:D, G2)
3​	Feb	5416​
4​	Mar	5760​
5​	Apr	5529​
6​	May	5717​
7​	Jun	5558​
8​	Jul	5718​
9​	Aug	5650​
10​	Sep	5619​
11​	Oct	5718​
12​	Nov	5607​
13​	Dec	5740​
14​		67735 ​	H14: =SUM(H2:H13)

The results were near instantaneous.

 

[doug firr]

Interesting, might give that a try. I wish pivot tables just had a "count distinct" values though, would make life easier. Thanks for your response.