Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advantage of Database functions | Excel Worksheet Functions | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions |