Count occurrences of unique values in a column in comparison with another column input

[santanuKD]

Hi,

I am looking for the Count of occurrences of unique values in column B in comparison with input selected from column A.
I have used the sumproduct Frequency formula to caluculate the unique values from one column. Please enhance for a selected value from column A.

Attached file for the need. Please advice

Thanks

[Kevin UK]

Hi santanuKD

Try the following array formula in F10, to commit the formula, CTRL + SHIFT + ENTER.

Please Login or Register  to view this content.

[Tony Valko]

Here's another one...

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A15=E10,MATCH(B2:B15,B2:B15,0)),ROW(B2:B15)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[santanuKD]

Hey Kevin,

Somehow it did not work when I give Level 3 in the selection. Please have a look.

Thanks,
Santanu

[santanuKD]

Unique Counts - Sumproduct_v1.1.xlsxDear Tony, Kevin,

if we have one more filter to be added, meaning based on the combination from column a & b the unique count from column c should be displayed.
Please have a look at the attached excel.

Thanks,
Shan

[Fotis1991]

Does this ARRAY formula works for you?

=SUM(IF((A2:A15=F21)*(B2:B15=G21),1/COUNTIFS(A2:A15,F21,B2:B15,G21,C2:C15,C2:C15)),0)

[Tony Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(A2:A15=F21,IF(B2:B15=G21,MATCH(C2:C15,C2:C15,0))),ROW(C2:C15)-ROW(C2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Kevin UK]

Hi santanuKD

Try the following array formula H21 (CSE to commit).

=SUM(SIGN(FREQUENCY(IF((A2:A15=F21)*(B2:B15=G21),MATCH(C2:C15,C2:C15,0)),ROW(2:15)-ROW(1:1))))

[santanuKD]

Dear All,

All the above mentioned formulas works well. This indeed helps. The below would be nice to have. Please suggest.

Problem: The excel I am using for this has 35000 rows of data with multiple columns. Hence any action I perform the excel gets hang for say 8-10 mins and I could not do any other thing. This is because of the calculation time that takes for the above formula which searches through 35000. Is there a way to come out of this issue. Can we achieve the same using macro or pivot?

Thanks