Excel count unique in range if

Question

What formula can be used to count unique values in column A if Column B is a value. i have seen many online but none of them seem to work when only using Column ranges , the data will be of varied length so only column ref can be used

this worked

=SUMPRODUCT((A1:A18<>"")*(B1:B18=$D$1)/COUNTIF(A1:A18,A1:A18))

but not when changed to

=SUMPRODUCT((A:A<>"")*(B:B=$D$1)/COUNTIF(A:A,A:A))

table

  A           B         C         D          E
12/12/2015  criteria1           criteria1      3
12/12/2015  criteria1           
13/12/2015  criteria1           
13/12/2015  criteria1           
14/12/2015  criteria1           
14/12/2015  criteria1           
18/12/2015  criteria2           
19/12/2015  criteria2           
20/12/2015  criteria2           
21/12/2015  criteria2           
22/12/2015  criteria2           
23/12/2015  criteria2           
24/12/2015  criteria3           
25/12/2015  criteria3           
26/12/2015  criteria3           
27/12/2015  criteria3           
28/12/2015  criteria3           
29/12/2015  criteria3

I Have also tried sumproduct with frequency but always got N/A

Sumproduct can't use whole column references. You'll have to find a different approach. — sous2817
– sous2817, Commented Jul 6, 2015 at 20:44
"A further improvement is that in Excel 2007, SUMPRODUCT can address a whole column" , is this not true? — Steven Martin
– Steven Martin, Commented Jul 6, 2015 at 20:47
I get a #DIV/0 error. I wonder if it's a memory issue. SUMPRODUCT is evaluating all 1,000,000+ rows of data, so while you may be able to use whole column references with Sumproduct, I don't think it's the right approach. — sous2817
– sous2817, Commented Jul 6, 2015 at 20:55
The fact that the dataset is of varied length does not at all mean that entire columns should be used as a reference. Do you really have data that potentially extends up to row one million-plus? Can you not set a lower upper bound than that? Even better, of course, is to define your range as a dynamic one, which automatically expands/contracts as the data changes. Do you know how to do this? Finally, this SUMPRODUCT/COUNTIF combination is astonishingly slow. FREQUENCY and MATCH is far quicker for this. — XOR LX
– XOR LX, Commented Jul 6, 2015 at 22:07

Brendan · Accepted Answer · 2015-07-06 22:23:54Z

1

If your data are in A1:B18, and criteria1 is in C1, try:

{=SUM(IF(FREQUENCY(IF(A1:A18<>"", IF(B1:B18=C1, MATCH(A1:A18, A1:A18, 0))), ROW(A1:A18)-ROW(A1)+1), 1))}

Expanded:

{=SUM(
    IF(
        FREQUENCY(
            IF(A1:A18<>"", 
                IF(B1:B18=C1, 
                    MATCH(A1:A18, A1:A18, 0)
                )
            ), 
            ROW(A1:A18)-ROW(A1)+1
        ), 
    1)
)}

Enter as an array formula with CtrlShiftEnter

edited Jul 6, 2015 at 22:23

answered Jul 6, 2015 at 22:13

Brendan

4,0111 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XOR LX Over a year ago

Good construction. Hopefully now the OP can just realise the error in their ways re referencing entire columns!

Collectives™ on Stack Overflow

Excel count unique in range if

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related