Posted to microsoft.public.excel.misc
|
|
Counting Two Characteristics from Two different Columns
Thanks Bernard ... this works. Cheers P
"Bernard Liengme" wrote:
I will assume the first rep "Bob" is in A10,the last in A100
For Tim, total clients: =COUNTIF(A10:A100,"Tim"
Or is A2 hold the value Time then =COUNTIF(A10:A100, A2)
Tim/Inactive
Again with Tim in A2: =SUMPRODUCT((A10:A100=A2),(C10:C100="Inactive")
or if you have Excel2007: COUNTIFS(A10:A100,A2,C10:C100,"Inactive) -- note
the S on COUNTIFS
When you have this working, have a look at Pivot tables
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...le-parameters/
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"PGiessler" wrote in message
...
I have a spreadsheet that lists Account Rep names in Column "A", Client
Names
in Column "B" and client status in Column "C" (i.e. active or inactive)
Bob | Medco | Active
Tim | Johnson | Inactive
Tim | P-Products| Active
Jill | Kaystar | Active
Tim | Gemni | Active
Bob | Transcorp | Inactive
At the top of the Spreadsheet I want to tally the information to indicate
that
Tim has three clients, 2 Active Client and 1 Inactive client
I believe I have done this before, but cannot for the life of me remember
what I did - I have tried to do a SUMPRODUCT but keep getting a zero
result
so something is amiss in my formula.
Help would be much appreciated.
.
|