Count with difficult/multiple citeria

G

GregA

Hi,

I have a complicated question for help. I hope that I make it sound ok.



First thing to remember is that this sheet is linked to another, which
is in-turn link to another...ive been filtering data!


so what i have is:


From B3:B200 a list of brands
From column F2 to to Z2 I have a list of shop names and then their respective sales in F3:Z200

What I want is for it to idenitfy where there are only sales in one
store (so where there are it will say one)...that is easy I can do
that! but the next bit is where I am stuck.


The brands can be the same, so there could be more than 1 brand
matching in column B (B3:B200), and there could be sales in different
shops, e.g. B52 could be the same as B114, but they could have sales
values in different shop cells, eg. H52 and X114. Thus, I would want
this to tell me that this is false. However, if it were to say that B52
and B114 (which are the same) are selling in H52 and H114 respectively,
then it would return a 1 value.


Thus, what I should have is, in say column AX (AX3:AX200) either a 1 or

a False value.


I am stuck because there could be one brand, there could be two, or
three,
or even up 10 of the same brand, but they might be selling in different

shops, i want to identify which are only selling in one.


If there is anyone out there, that understood that! lol, please please
I would really appreciate your help!


Greg.
 
B

Bob Phillips

What is wrong with the suggestion that I gave yesterday?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

GregA

I think the sumproduct doesn't reply the answer that ?I would like

E.g. the function you gave me was:

=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUMN(F$3:Z$3)))=1,1)

However, if I have three of the same brands in column b, and they are
all selling in shop a in column f, it should return a 1 value, but with
this formula it returns a false value. Thus, this formula doesn't
appear to be taking account of multiple items in column b3:b200.


If you have any other suggestions, i would like to hear from you. I am
currently working on these two options, but they are only returning
FALSE values for every cell once activated with the ctrl+shift+enter
process:

=IF(COUNTDIFF(IF((B$3:B$200=B3)*(F$3:Z$200)>0,F$2:Z$2,""),,"")=1,1)

=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUMN(F$3:Z$3)))=1,1)



Many Thanks, Greg.
 
B

Bob Phillips

Not me pal, the answer I gave was

=IF(SUMPRODUCT(($B$3:$B$200=$B3)*($F$3:$Z$200))=1,"Only one","More than
one")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top