Get the unique count of column on the basis of other column's data

**pawasthi** · 06-30-2011, 12:52 PM

Hi All,

I have three columns (A,B,C) in sheet and I need to sum column C data where data in A = *AB* AND data in column B is "*ype1*"

A B C
ABC type1 1
DEF type2 0
ABC type2 1
XYZ type1 1
ABC type1 0
DEF type1 0

Also I need to count the unique values in column C when below two conditions are satisfied:

Cond 1: Column a has *AB*
Cond 2: Coumn B has *ype1*

Thanks in advance

**NBVC** · 06-30-2011, 01:09 PM

Try these:

=SUMIFS(C1:C6,A1:A6,"*AB*",B1:B6,"*ype1*")

and

=SUM(FREQUENCY(IF(ISNUMBER(SEARCH("*AB*",A1:A6)),IF(ISNUMBER(SEARCH("*ype1*",$B$1:$B$6)),MATCH(B1:B6,B1:B6,0))),ROW(B1:B6)-ROW(B1)+1))

this last formula needs to be confirmed with CTRL+SHIFT+ENTER to work, not just enter.

**pawasthi** · 06-30-2011, 03:40 PM

Originally Posted by NBVC

Try these:

=SUMIFS(C1:C6,A1:A6,"*AB*",B1:B6,"*ype1*")

and

=SUM(FREQUENCY(IF(ISNUMBER(SEARCH("*AB*",A1:A6)),IF(ISNUMBER(SEARCH("*ype1*",$B$1:$B$6)),MATCH(B1:B6,B1:B6,0))),ROW(B1:B6)-ROW(B1)+1))

this last formula needs to be confirmed with CTRL+SHIFT+ENTER to work, not just enter.

The second one is not giving the correct result. Because I need to unique value count in column "C" when both of the mentioned condition are satisfied.

Please correct me if I am doing something wrong.

thanks

**NBVC** · 06-30-2011, 03:51 PM

Try:

=COUNT(1/(FREQUENCY(IF(ISNUMBER(SEARCH("*AB*",A1:A6)),IF(ISNUMBER(SEARCH("*ype1*",$B$1:$B$6)),IF(C1:C6<>"",MATCH(C1:C6,C1:C6,0)))),ROW(B1:B6)-ROW(B1)+1)))

CSE confirmed

**pawasthi** · 06-30-2011, 04:11 PM

Originally Posted by NBVC

Try:

=COUNT(1/(FREQUENCY(IF(ISNUMBER(SEARCH("*AB*",A1:A6)),IF(ISNUMBER(SEARCH("*ype1*",$B$1:$B$6)),IF(C1:C6<>"",MATCH(C1:C6,C1:C6,0)))),ROW(B1:B6)-ROW(B1)+1)))

CSE confirmed

Wonderful!!!

You are awesome!!! Thanks for such valuable help in TIME

Thanks again