Counting uniques

G11=SUM(INDEX(($A$3:$A$21=$F11)/COUNTIFS($A$3:$A$21,$A$3:$A$21,$B$3:$B$21,$B$3:$B$21),0))
Copy towards down

Hi!!

Wow! thanks!! that is very helpful!!!

The only thing will be that... Do you think is there any way to simplify the formula? my original file has 87000 rows, and once I entered your formula my Excel crashes... (I think I am not messing up with the formula...but I need to double check... the thing is I dont get any error after entering the formula, it's just the computer starts "thinking" and excel says "not responding")

Thanks again!


Correction: I am not transfering your formula wrongly, just tried for a range of 100 rows and it worked perfectly, but the moment I try to extend it for 87000rows, my computer crashes... LOL,,, thats why maybe I am wondering maybe there is a way to simplify the formula?

Hi.

I'm not certain, though it's possible that this alternative construction, an array formula**, offers slightly better performance over large datasets:

=SUM(IF(FREQUENCY(IF($A$3:$A$21=J11,MATCH($B$3:$B$21,$B$3:$B$21,0)),ROW($B$3:$B$21)-MIN(ROW($B$3:$B$21))+1),1))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Interesting.

And do you have many other formulas in there besides this series?

I did an experiment where I extended the range to row 85000. Using nflsales' formula, my workbook crashed. Using the version I posted, however, and for the six names as given, full calculation took just under 0.5 seconds.

Regards

@nflsales

Surprising how vastly inefficient that set-up with COUNTIFS is, actually.

You'd think it would be the other way round, though the combination of FREQUENCY and MATCH is evidently a very fast one in terms of processing.

Regards

And to how many rows did you copy that formula?

And I take it you deleted nflsales' formulas first!!

Regards

Originally Posted by mglon

to 87174 rows like a boss... hehe

???

I thought it was the data source that was that number of rows? And that you only had a dozen names or so, i.e. a dozen rows' worth of formulas?

Regards

Just extended mine to 52 rows. Just over a second for full calculation with the formulas referencing a range of 85,000 rows.

Perhaps you'll have to replace any confidential information with dummy data and upload your actual workbook.

Regards

Sorry - you don't believe my statements re calculation times in my workbook?

Regards

Originally Posted by XOR LX

@nflsales

Surprising how vastly inefficient that set-up with COUNTIFS is, actually.

You'd think it would be the other way round, though the combination of FREQUENCY and MATCH is evidently a very fast one in terms of processing.

In addition, folks also think that the SUM(IF(FREQUENCY is an array formula therefore it must be slower to calculate.

Not true!

Originally Posted by Tony Valko

In addition, folks also think that the SUM(IF(FREQUENCY is an array formula therefore it must be slower to calculate.

Not true!

True in general, but with a preceding conditional clause, i.e.:

SUM(IF(FREQUENCY(IF($A$2:$A$84999=F2,...

as here, it must be committed with CSE.

Cheers

There is calculation timer code here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

You can run the tests and see for yourself. I do this kind of testing quite often.

Counting uniques in Excel is "slow" by computing standards, especially on large datasets.

In general, the SUM(IF(FREQUENCY syntax is faster to calculate compared to other formula syntaxes.

There are UDFs that are amazingly fast at this but you either have to know how to write (program) them or get them through add-ins.

@mglon

Unfortunately it exceeded forum limits at 80,000 rows. Have attached a version with 50,000 rows worth of data.

Regards

Originally Posted by Tony Valko

In general, the SUM(IF(FREQUENCY syntax is faster to calculate compared to other formula syntaxes.

And, as I said, much, much quicker than the equivalent 1/COUNTIFS set-up, which is horrendously slow!

Originally Posted by XOR LX

True in general, but with a preceding conditional clause, i.e.:

SUM(IF(FREQUENCY(IF($A$2:$A$84999=F2,...

as here, it must be committed with CSE.

Yeah, I wasn't claiming the formula wasn't an array entered formula.

I meant that just because it is an array entered formula doesn't mean it's automatically slower to calculcate compared to a non-array entered formula.

Originally Posted by Tony Valko

Yeah, I wasn't claiming the formula wasn't an array entered formula.

I meant that just because it is an array entered formula doesn't mean it's automatically slower to calculcate compared to a non-array entered formula.

Ah - sorry! Then yes - totally agreed! Array formulas are evil monsters which, unless stopped, will take over the internal workings of the world's computer nexus and reduce our state of technological advancement to little more than that of the neanderthals.

...and all I thought we had to fret about was the merged cell...

Perhaps an array formula in a merged cell is the ultimate Excel chimera?

I'll never set foot outdoors again...

You only paste it into the first cell, then follow my previous instructions on how to commit it as an array formula, and then copy it down.

Regards

Sincere apologies.

The data I used for testing was not a fair sample, as the dummy values I entered in the City column were from a set of only 40 or 50, and so were all matched linearly quite early on in the column, thus greatly reducing the amount of calculation Excel needed to perform.

For such a dataset, I cannot think of a feasible way in which you could obtain your results within a reasonable calculation time using worksheet formulas alone. I can only suggest that you re-post in the VBA section in the hope that such an approach will be of benefit.

Regards

Not at all. Like I said, apologies for having misinformed you, and hope you'll find a workaround of some sorts.

Regards