Counting Two Characteristics from Two different Columns

[PGiessler]

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.

 

[Bernard Liengme]

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/2005/06/23/download-pivottable-parameters/

best wishes

 

[Bernard Liengme]

Missing quote
or if you have Excel2007: COUNTIFS(A10:A100,A2,C10:C100,"Inactive")
Bernard

 

[Jim Thomlinson]

I would be inclined to use a pivot table for this... Highlight the data that
you want to summarize including the headings. Select data -> Pivot Table... |
You can just select Finish.

Drag the Account Rep field to the left column. Drag the Active / Inactive
Field next to the Account Rep in the left column. Now drage Active / Inactive
into the data area. That should do it... You can select an auto format to
make it look fancy. Alternativley the Active / Inactive could be dragged to
the upper row.

 

[Robbro]

To count Tim's clients use countif(A:A,"Tim") to count Tim's Active clinets
do countifs(A:A,Tim,C:C,"Active")
I think those are the fomulaic solutions to your problems assuming your
version of office supports countifs.

 

[PGiessler]

Thanks Bernard ... this works. Cheers P

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/2005/06/23/download-pivottable-parameters/

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



.