Total number of occurances for two text values

M

MartiC

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
 
M

Max

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.
 
T

Teethless mama

F2: holds LJ
G2: holds R. Lee

=COUNT(INDEX(SEARCH(F2,C2:C7)/(D2:D7=G2),0))
 

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