How to count unique values with multiple criteria

[rover]

Hi, iam a new user here

I am working on a keyword template which is almost completed but iam stuck at counting unique values in a given range with multiple criteria, i hope some one here can help me solve the problem. to give a clear picture the following is the data range.(or you can use the attached excel)

a1:a10
Date:
Jan
Jan
Feb
Mar
Feb
Jan
Jan
Jan
Feb

b1:b10
Keyword:
pavilion
touch smart
asus
studio
slimline
pavilion
nvidia
xfx
xblade

c1:c10
Type:
PC
PC
pc
Laptop
PC
PC
pc
pc
Server

d1:d10
Brand:
HP
HP
non brand
dell
HP
HP
non brand
non brand
IBM

Criteria:
Jan>PC>Brand=?
g7=Jan
i5=PC
by using the below formula i got the result as 3 (Under Jan we have toal of 4 PC out of which only 3 are Brand)
{=SUM(IF((A2:A10=G7)*(C2:C10=I5)*(D210<>"non brand"),1,0))}

Jan>PC>Brand>unique_keyword=?
but the desired result should be 2 as there are only 2 unique Keyword out of 3 Brand

i know this formula to count unique values but i don not know how to combine it with the above formula
{=SUM(IF(FREQUENCY(IF(LEN(B2:B10)>0,MATCH(B2:B10,B 2:B10,0),""), IF(LEN(B2:B10)>0,MATCH(B2:B10,B2:B10,0),""))>0,1)) }

hope this can be achieved

thank you in advance

Vivek

 

[rover]

i got the solution for this in a different forum, i thought i will share it here

http://forums.techonthenet.com/showthread.php?t=1068

below is the comment from the user flakeydove1994

"You may need to insert 2 columns

In column E put:

=A2&B2&C2&D2

and in column F put:

=IF(COUNTIF(E$2:E2,E2)=1,1,0)

Then your formula becomes:

{=SUM((A2:A10=G7)*(C2:C10=I5)*(D210<>"non brand")*(F2:F10))}

Joe"

or you could also try this formula, but this will take up a lot of system resource.

{=SUM(IF(FREQUENCY(IF((A2:A10=G7)*(C2:C10=I5)*(D210<>"non brand"),IF(B2:B10<>"",MATCH(B2:B10,B2:B10,0))),ROW (B2:B10)-ROW(B2)+1),1))}


thought someone might find it useful

thank you for reading my post.

Vivek