1

Source File Calculation sheet

Hi, I'm trying to calculate the unique counts of column C of my source file Product (Import ID) with criteria it matches column B - Manufacturer (Import ID). Googled around and used below formula but returned no results. Anyone could help me where went wrong ??

=SUM(IF(FREQUENCY('Report 1'!C:C,'Report 1'!C:C)>0,('Report 1'!B:B='mapped stat'!B55)*1,0))

Where Report1 column C is the unique count range I'm trying to count & Report1 column B is the condition/criteria I'm trying to match with my calculation sheet (mappedstat)

Dixon Leong
  • 21
  • 5
  • When you say it returned no results did you get 0 or #REF or something else? Are your IDs numeric or text? – Carol Nov 28 '17 at 04:33
  • Possible duplicate of [Count Unique values with a condition](https://stackoverflow.com/questions/15944249/count-unique-values-with-a-condition) – underscore_d Apr 19 '18 at 14:56

3 Answers3

3

There is a standard COUNTUNIQUE method using SUMPRODUCT and COUNTIF functions; (e.g. SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&"")) ). This can be expanded to include conditions by changing to a COUNTIFS function but care must be made to ensure that no #DIV/0! error can occur.

=SUMPRODUCT(('R1'!B1:B12=B55)/(COUNTIFS('R1'!C1:C12,'R1'!C1:C12&"",'R1'!B1:B12, B55)+('R1'!B1:B12<>B55)))

Avoid full column range references.

  • Thanks, it works, I have removed the full column range to static range. – Dixon Leong Nov 30 '17 at 01:58
  • But what do you meant by no #DIV/0! error ? under what scenario will these occur? – Dixon Leong Nov 30 '17 at 01:59
  • I indeed have 1 row record which returns #DIV/0! error, how I go about to resolve this ? – Dixon Leong Nov 30 '17 at 03:26
  • For an explanation on avoiding #DIV/0! errors see https://stackoverflow.com/questions/50030971/counting-number-of-distinct-people-successfully-contacted-in-a-date-range-excel/50034006#50034006 –  Jul 13 '18 at 08:34
1

the formula is working now (exception to a record with div error): =SUMPRODUCT(('Report 1'!$B$2:$B$10829=B55)/(COUNTIFS('Report 1'!$C$2:$C$10829,'Report 1'!$C$2:$C$10829&"",'Report 1'!$B$2:$B$10829, B55)+('Report 1'!$B$2:$B$10829<>B55)))

Would there be another less resource hungry formula ? My data set has around 10k rows x 40 columns, now each time an edit on data source is made, excel will hang as for about 2-3 mins for calculating the formulas.

I googled around the use of frequency and match , and come up with below formula, not sure where went wrong, it doesn't return a value

=SUM(--(FREQUENCY(IF('Report 1'!$C$2:$C$10829<>"",IF('Report 1'!$B$2:$B$10829=B55,MATCH('Report 1'!$C$2:$C$10829,'Report 1'!$C$2:$C$10829,0))),ROW('Report 1'!$C$2:$C$10829)-ROW('Report 1'!C2)+1)>0))

If possible, would like to retain / modify as little as possible for the mappedstat worksheet, as this is linked to a powerbi dashboard

Dixon Leong
  • 21
  • 5
  • That version with `FREQUENCY` should be faster than the one using `COUNTIFS`. Formula looks OK to me but you have to confirm with `CTRL+SHIFT+ENTER` so that you get curly braces like { and } around the formula - also you won't get #DIV/0 errors – barry houdini Nov 30 '17 at 14:31
0

My go-to formula for calculating all things unique, distinct, or duplicate isn't a formula at all...it's a PivotTable.

Whip up a PivotTable, put Manufacturer in the ROWS pane, put Product in the Values area and change the aggregation to COUNT. You will get counts of Product by Manufacturer, and from that you can easily apply a Values filter to look for duplicates (where count will be > 1) or unique (where count = 1) values.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27