Count unique values in column with few criterias with formula (without ctrl+shift+enter)

[rost1]

Hi everyone!

I need to count by non-array formula (w/o ctrl+shift+enter) quantity of unique items in column ("centre) that satisfy special criteria in column "month", as shown below:

centre	month #
1101	1
1102	1
1101	1
1103	1
1101	2
1102	2
1102	2

<tbody>
</tbody>

Answer (e.g. for Jan): unique qty of centres in Jan (#1) = 3 centres.

There are few possible ways to do it via using sumproduct+countif or sum+if+frequency formulas, but all of them require ctrl+shift+enter that significantly slow down calculations for large massive of data.

Please, provide easier suggestions to resolve this issue.
Thanks in advance!

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	centre​	month #​			month	# distinct centre
2​	1101​	1​			1​	3​
3​	1102​	1​			2​	2​
4​	1101​	1​
5​	1103​	1​
6​	1101​	2​
7​	1102​	2​
8​	1102​	2​

In F2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$8=$E2,$A$2:$A$8),$A$2:$A$8),1))

This formula assumes that centre ids are numbers, not text.

 

[rost1]

Thank you for the prompt answer. The matter is that I am looking for way to find solution without using of control+shift+enter

 

[Aladin Akyurek]

Thank you for the prompt answer. The matter is that I am looking for way to find solution without using of control+shift+enter

This is the most efficient formula. See: http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html, which also list an array-processing formula with SumProduct for which no control+shift+enter is needed for confirmation.

 

[XLearner]

Which version of Excel do you run?

 

[rost1]

2013

 

[XLearner]

2013

So have a look to article Distinct Count in Pivot Tables – Finally in Excel 2013 (with Month# as Filter in your case)

Regards
XLearner

 

[XLearner]

As a side note it appears that PowerPivot for Excel 2010 also allows Distinct Count (the measure needs to be created though).
See para. Video: Count Unique Items with PowerPivot on the Contextures/Excel Pivot Table - Grouping Data page

 

[rost1]

Formula is preferred. Its seems to be that array formula is obligated

 

[Aladin Akyurek]

Formula is preferred. Its seems to be that array formula is obligated

The FREQUENCY formula is really faster than a COUNTIF(S) formula.