Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total number of occurances for two text values
Here is my sample data:
A B C D 1 Date Name Rec VP 2 2/1 Jane Doe EM F. Charles 3 2/1 Don Duck LJ R. Lee 3 2/2 Minnie Lou SH F. Charles 4 2/2 Angel Wing EM R. Lee 5 2/5 Chris Carol LJ R. Lee 6 2/7 Holly Bugge LJ F. Charles What I would like to be able to do is input a formula that will be able to calculate the number of times an value in column C & column D occur (ie: LJ + R. Lee = 2, LJ + F. Charles = 1). I am able to get a total of how many times LJ occurs in column C or how many times R. Lee occurs in column D but not how many times LJ & R. Lee occur at the same time. Does this make sense & can anyone help? Thanks, Marti |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total number of occurances for two text values
Source table assumed in cols A to D, data from row2 down
In say, H2: =SUMPRODUCT(($C$2:$C$100=F2)*($D$2:$D$100=G2)) where in F2, G2 down are "Rec" & "VP" values will return the required results. Copy H2 down. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MartiC" wrote: Here is my sample data: A B C D 1 Date Name Rec VP 2 2/1 Jane Doe EM F. Charles 3 2/1 Don Duck LJ R. Lee 3 2/2 Minnie Lou SH F. Charles 4 2/2 Angel Wing EM R. Lee 5 2/5 Chris Carol LJ R. Lee 6 2/7 Holly Bugge LJ F. Charles What I would like to be able to do is input a formula that will be able to calculate the number of times an value in column C & column D occur (ie: LJ + R. Lee = 2, LJ + F. Charles = 1). I am able to get a total of how many times LJ occurs in column C or how many times R. Lee occurs in column D but not how many times LJ & R. Lee occur at the same time. Does this make sense & can anyone help? Thanks, Marti |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total number of occurances for two text values
Try this:
F2 = LJ G2 = R. Lee =SUMPRODUCT(--(C2:C7=F2),--(D2:D7=G2)) -- Biff Microsoft Excel MVP "MartiC" wrote in message ... Here is my sample data: A B C D 1 Date Name Rec VP 2 2/1 Jane Doe EM F. Charles 3 2/1 Don Duck LJ R. Lee 3 2/2 Minnie Lou SH F. Charles 4 2/2 Angel Wing EM R. Lee 5 2/5 Chris Carol LJ R. Lee 6 2/7 Holly Bugge LJ F. Charles What I would like to be able to do is input a formula that will be able to calculate the number of times an value in column C & column D occur (ie: LJ + R. Lee = 2, LJ + F. Charles = 1). I am able to get a total of how many times LJ occurs in column C or how many times R. Lee occurs in column D but not how many times LJ & R. Lee occur at the same time. Does this make sense & can anyone help? Thanks, Marti |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total number of occurances for two text values
F2: holds LJ
G2: holds R. Lee =COUNT(INDEX(SEARCH(F2,C2:C7)/(D2:D7=G2),0)) "MartiC" wrote: Here is my sample data: A B C D 1 Date Name Rec VP 2 2/1 Jane Doe EM F. Charles 3 2/1 Don Duck LJ R. Lee 3 2/2 Minnie Lou SH F. Charles 4 2/2 Angel Wing EM R. Lee 5 2/5 Chris Carol LJ R. Lee 6 2/7 Holly Bugge LJ F. Charles What I would like to be able to do is input a formula that will be able to calculate the number of times an value in column C & column D occur (ie: LJ + R. Lee = 2, LJ + F. Charles = 1). I am able to get a total of how many times LJ occurs in column C or how many times R. Lee occurs in column D but not how many times LJ & R. Lee occur at the same time. Does this make sense & can anyone help? Thanks, Marti |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
total cells with text and number EX: GS-5 | Excel Worksheet Functions | |||
count duplicates/total number of occurances | Excel Discussion (Misc queries) | |||
Add total number of rows (text) in a column | Excel Worksheet Functions | |||
Count occurances of multiple values | Excel Worksheet Functions |