about functions...
 

ok, guys, i need your help... i have an excel file and i can't find a
function i need...


i have two columns, not adjacent.. the first one let's say that has text
cells...so ,does the second one..those texts are periodically repeated...
there are all kinds of combinations in all rows... we're talking about
approximately 1300 rows...the first column has 386 different values and the
second one 5...you do the math...

what i need is a possibly COUNT function that would count "duh.." the number
of cells in the second column that have the same value in the left
column...

e.g. let's say that the first column has the values A,B,C,D,E.. i remind you
that's text.. and the second one has F,G,H,I,J.. my database is sth like
this:


column A Column D

C G
A J
A H
E H
D J

and this goes on with any possible combinations... i need to count the
number of e.g. H cells of column D that have in the same row e.g A cells..

i know, it sounds easy but it's not to me.. i tried out some COUNT functions
but either i do sth wrong or maybe it needs a different function or a
nested one...

so, if have some spare time i would really appreciate it you guys...

thanx in advance...

about functions...
 

for your example: set up a column with unique values from D Say in AA and
another with unique values from A say in AB
Copy and sort such that there are all possible pairs.
in AC1 enter
=sumproduct(--(D$1:D$1300=AA1),--(A$1:A$1300=AB1)
copy down to the bottom of your unique pair combinations.

"toolis" wrote:

ok, guys, i need your help... i have an excel file and i can't find a
function i need...


i have two columns, not adjacent.. the first one let's say that has text
cells...so ,does the second one..those texts are periodically repeated...
there are all kinds of combinations in all rows... we're talking about
approximately 1300 rows...the first column has 386 different values and the
second one 5...you do the math...

what i need is a possibly COUNT function that would count "duh.." the number
of cells in the second column that have the same value in the left
column...

e.g. let's say that the first column has the values A,B,C,D,E.. i remind you
that's text.. and the second one has F,G,H,I,J.. my database is sth like
this:


column A Column D

C G
A J
A H
E H
D J

and this goes on with any possible combinations... i need to count the
number of e.g. H cells of column D that have in the same row e.g A cells..

i know, it sounds easy but it's not to me.. i tried out some COUNT functions
but either i do sth wrong or maybe it needs a different function or a
nested one...

so, if have some spare time i would really appreciate it you guys...

thanx in advance...

about functions...
 

=SUMPRODUCT(--(a1=$A$5:$A$1000),--(d1=$D$5:$D$1000))

then type in cells a1 and d1 the values you are looking for in each column

adapt as required
--
Allllen


"toolis" wrote:

ok, guys, i need your help... i have an excel file and i can't find a
function i need...


i have two columns, not adjacent.. the first one let's say that has text
cells...so ,does the second one..those texts are periodically repeated...
there are all kinds of combinations in all rows... we're talking about
approximately 1300 rows...the first column has 386 different values and the
second one 5...you do the math...

what i need is a possibly COUNT function that would count "duh.." the number
of cells in the second column that have the same value in the left
column...

e.g. let's say that the first column has the values A,B,C,D,E.. i remind you
that's text.. and the second one has F,G,H,I,J.. my database is sth like
this:


column A Column D

C G
A J
A H
E H
D J

and this goes on with any possible combinations... i need to count the
number of e.g. H cells of column D that have in the same row e.g A cells..

i know, it sounds easy but it's not to me.. i tried out some COUNT functions
but either i do sth wrong or maybe it needs a different function or a
nested one...

so, if have some spare time i would really appreciate it you guys...

thanx in advance...