Counting Two Characteristics from Two different Columns

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.
0
Utf
2/25/2010 3:51:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1024 Views

Similar Articles

[PageSpeed] 36

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

"PGiessler" <PGiessler@discussions.microsoft.com> wrote in message 
news:0DD50DCB-4168-4B14-97F0-D06523093F05@microsoft.com...
> 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. 

0
Bernard
2/25/2010 4:08:36 PM
Missing quote
 or if you have Excel2007: COUNTIFS(A10:A100,A2,C10:C100,"Inactive")
Bernard

"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in message 
news:eg#wITjtKHA.6140@TK2MSFTNGP05.phx.gbl...
> 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
>
> "PGiessler" <PGiessler@discussions.microsoft.com> wrote in message 
> news:0DD50DCB-4168-4B14-97F0-D06523093F05@microsoft.com...
>> 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.
> 
0
Bernard
2/25/2010 4:28:48 PM
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.
-- 
HTH...

Jim Thomlinson


"PGiessler" wrote:

> 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.
0
Utf
2/25/2010 4:41:03 PM
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" wrote:

> 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.
0
Utf
2/25/2010 4:49:01 PM
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/2005/06/23/download-pivottable-parameters/
> 
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> "PGiessler" <PGiessler@discussions.microsoft.com> wrote in message 
> news:0DD50DCB-4168-4B14-97F0-D06523093F05@microsoft.com...
> > 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. 
> 
> .
> 
0
Utf
2/25/2010 5:52:01 PM
Reply:

Similar Artilces:

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Count Age Grouping
I have an access 2k database in which I need to count groups of records of individuals by that age groups such as 14- 20 no of individuals 21-30 no of individuals 31-40 no of individuals 41-50 no of individuals 51-60 no of individuals 61-70 no of individuals 71-80 no of individuals 80+ no of individuals I have both DOB and Age fields in the table I have tried several queries but with no luck and ideas On 19 Mar 2007 16:51:49 -0700, "Nemesis_uk" <nemesis_uk@ntlworld.com> wrote: >I have an access 2k ...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Users cannot login to OWA after Exchange 2K3 was moved into different OU
I created a new OU named member servers, and moved the exchange 2k3 server into it. After I moved it, only the administrator was able to login to OWA successfully. No users could login to OWA. I created a test account with a mailbox, and was able to login to OWA with the newly created test account. I then moved the exchange 2k3 server from the member servers OU into the computers OU, and the newly created account was unable to login to OWA. Any help with this would be appreciated. ...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

Selecting a column with an integer
Sub ColumnSelection() ' Selecting a column with an integer ' Please show me how to eliminate the use of Cells(1, 1) Dim r As Integer Dim c As String Dim numericcolumn As Integer Dim alphabetcolumn As String numericcolumn = 4 ' in practice 4 is the resultant of an equation alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")" Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of Cells(1, 1) c = Cells(1, 1).Value ' I like to eliminate the use of Cells(1, 1) Cell...

Are there MFC subclasses that give a different "look and feel" to the UI?
I was wondering if there are free implementations of MFC subclasses of windows and controls that give you a different look and feel? I like the Windows 2000 look, but it's quite a bit dated now, and the XP look is hideous for my taste, so I'm looking for something new, more modern looking, sleek. Thanks in advance for any suggestions. Franco See if this has something your looking for. http://www.prof-uis.com/ "Franco" <cuminato@yahoo.com> wrote in message news:1149800640.987272.304540@c74g2000cwc.googlegroups.com... >I was wondering if there are free implementa...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...

Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific column. The code refers to the specific range, but yet it prevents copying and pasting on the whole worksheet. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("H:H")) Is Nothing Then Application.CellDragAndDrop = False Application.CutCopyMode = False Else Application.CellDragAndDrop = True End If End Sub Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 11:42:01...