how to summarize total number of appearance in a column?

Can you tell me how to summarize total number of appearance in a column?
For example, I have a column with three names appear at different rows 
another column with value to the names:

    John        45
    Mary        15
    John        33
    Rick        10
    Rick        11
    Mary        31
    Mary        22
    John        0

How can I summarize: Number of times John appears (3 times),
and the total number in John's row (45+33+0=78).

Thank you. 

0
nospam7515 (2086)
12/19/2007 5:29:54 PM
excel 39879 articles. 2 followers. Follow

3 Replies
392 Views

Similar Articles

[PageSpeed] 4

=COUNTIF(A1:A10,"John")

=SUMIF(A1:A10,"John",B1:B10)

if you replace "John" with a cell like C1 you can use


=COUNTIF(A1:A10,C1)


=SUMIF(A1:A10,C1,B1:B10)


and just put the criteria in C1


-- 


Regards,


Peo Sjoblom


"Alberta H K" <nospam@nospam.com> wrote in message 
news:O%23rKPTmQIHA.3388@TK2MSFTNGP03.phx.gbl...
> Can you tell me how to summarize total number of appearance in a column?
> For example, I have a column with three names appear at different rows 
> another column with value to the names:
>
>    John        45
>    Mary        15
>    John        33
>    Rick        10
>    Rick        11
>    Mary        31
>    Mary        22
>    John        0
>
> How can I summarize: Number of times John appears (3 times),
> and the total number in John's row (45+33+0=78).
>
> Thank you. 


0
terre081 (3244)
12/19/2007 5:36:29 PM
Thank you Peo.

Can you give me an example of criteria in C1 should be?
Should C1 be a number?

Thank you. Alberta

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:ub%23HxWmQIHA.1208@TK2MSFTNGP05.phx.gbl...
> =COUNTIF(A1:A10,"John")
>
> =SUMIF(A1:A10,"John",B1:B10)
>
> if you replace "John" with a cell like C1 you can use
>
>
> =COUNTIF(A1:A10,C1)
>
>
> =SUMIF(A1:A10,C1,B1:B10)
>
>
> and just put the criteria in C1
>
>
> -- 
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
> "Alberta H K" <nospam@nospam.com> wrote in message 
> news:O%23rKPTmQIHA.3388@TK2MSFTNGP03.phx.gbl...
>> Can you tell me how to summarize total number of appearance in a column?
>> For example, I have a column with three names appear at different rows 
>> another column with value to the names:
>>
>>    John        45
>>    Mary        15
>>    John        33
>>    Rick        10
>>    Rick        11
>>    Mary        31
>>    Mary        22
>>    John        0
>>
>> How can I summarize: Number of times John appears (3 times),
>> and the total number in John's row (45+33+0=78).
>>
>> Thank you.
>
> 

0
nospam7515 (2086)
12/19/2007 7:09:49 PM
In your example you should put John in C1


-- 


Regards,


Peo Sjoblom


"Alberta H K" <nospam@nospam.com> wrote in message 
news:OXWkELnQIHA.4752@TK2MSFTNGP05.phx.gbl...
> Thank you Peo.
>
> Can you give me an example of criteria in C1 should be?
> Should C1 be a number?
>
> Thank you. Alberta
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message 
> news:ub%23HxWmQIHA.1208@TK2MSFTNGP05.phx.gbl...
>> =COUNTIF(A1:A10,"John")
>>
>> =SUMIF(A1:A10,"John",B1:B10)
>>
>> if you replace "John" with a cell like C1 you can use
>>
>>
>> =COUNTIF(A1:A10,C1)
>>
>>
>> =SUMIF(A1:A10,C1,B1:B10)
>>
>>
>> and just put the criteria in C1
>>
>>
>> -- 
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>> "Alberta H K" <nospam@nospam.com> wrote in message 
>> news:O%23rKPTmQIHA.3388@TK2MSFTNGP03.phx.gbl...
>>> Can you tell me how to summarize total number of appearance in a column?
>>> For example, I have a column with three names appear at different rows 
>>> another column with value to the names:
>>>
>>>    John        45
>>>    Mary        15
>>>    John        33
>>>    Rick        10
>>>    Rick        11
>>>    Mary        31
>>>    Mary        22
>>>    John        0
>>>
>>> How can I summarize: Number of times John appears (3 times),
>>> and the total number in John's row (45+33+0=78).
>>>
>>> Thank you.
>>
>>
> 


0
terre081 (3244)
12/19/2007 8:42:04 PM
Reply:

Similar Artilces:

auto filling columns from rows
Hi there How can I auto fill a series of values that equals a series of cells from another sheet? The cells I want the information to come from a row in another sheet and are going into a column. the references of the host cell are '!AH$9 when I fill down using the plus sign at the bottom corner it just copies this reference to each cell instead of filling the values '!AI$9 then '!AJ$9 etc. Ken, Try taking the dollar signs out before you copy the formula. The dollar signs make the formula absolute, which means that no matter where you copy it to, you'll always be referen...

Difference between numbers
I have a query that outputs "student name" and "entry#" Entry# are in ascending order. Some students may only have 1 entry# or multiple entry#'s depending on how many routines they are in. What I need to be able to do is calculate the difference between studen'ts entry#'s and see if they have at least 5 entries between them. Is this possible? Not sure that I'm understanding this correctly - to me, "difference" implies a mathematical calculation (subtraction). But if what you're wanting to get is a list of student names for ...

How do I assign sequential numbers in an invoice?
Creating a simple invoice in Excel. Want to assign unique number to each invoice, sequentially as each is created. IHow can I ope a file saved in DBASE 4 version in MS Excel 2000? -- Dan I.A.E (Nigeria, (234)08036073033) "halex" wrote: > Creating a simple invoice in Excel. Want to assign unique number to each > invoice, sequentially as each is created. Info here - http://www.mcgimpsey.com/excel/udfs/sequentialnums.html Free template here - http://tinyurl.com/dsoz -- cycling-rod Take a look here: http://www.mcgimpsey.com/excel/sequentialnums.html In arti...

How to Summarize Data in a sentence?
I know I've seen this, should have written it down at the time, but wasn't ready to implement it then, but now I need it. I want to take some various cells and create a new cell based on them, only have it display in a sentence. Something like "A 5 inch by 3 inch green sheet with rounded edges" where the values 5, 3, green, sheet, and rounded are stored in various cells and would of course change depending on other options. I know it's simple, but can't find anything in the help. Thanks in advance Conf. With the data in A1, B1, C1, D1, and E1: ="A &qu...

filling in a column
I have exported data which gives me details of invoices by client, however the client column is populated just once, while there can be more than one invoice per client. How can I create a macro that fills in the client column automatically up to the next client name and then continues to full the column with this next client until it finds no other client. I have tried to explain the issue below Column 1 Column 2 Client 1 Invoice 1 Invoice 2 Invoice 3 Client2 Invoice 4 Invoice 5 Clientn Invoice x How can I populate the Column 1 with the client nam...

How to convert numbers using dots into numbers using commas
I have an Excel sheet with a specific range of cells that contain numbers. The format of these cells is "General" (so not "Number"). The numbers in these cells use dots (e.g. 4.2) while I work with commas (e.g. 4,2). Does anyone know how can I convert the numbers into numbers that use commas? If your system is set to use commas as decimal point you should be able to select the range of cells with dots and Edit>Replace what: dot(.) with: comma(,) Replace all. Gord Dibben MS Excel MVP On Tue, 5 Dec 2006 14:49:01 -0800, Martin <Martin@discussions.microsoft.c...

Total Hours
Is there a way in excel to convert total hours. I'm trying to convert 8:07 to 8:25, 8:23 to 8:50, 8:37 to 8:45 and 8:55 to 9:00. Is this possible to do in excel. Floyd I'm sure it's possible, but I see no rhyme or reason for the "conversions" you're making. Do the converted values remain XL times? If so, why would 8:23 convert to 8:50, but 8:37 convert to 8:45? Please explain. In article <P6mdnUhULe8YkAyiRVn-jw@comcast.com>, "Floyd Forbes" <floydforbes@comcast.net> wrote: > Is there a way in excel to convert total hours. I'm...

Comparing and extracting from two columns
Hi all, I have two columns of data. Column A is a list of approx. 20,000 ZIP codes, column B is a selected list of approx. 1600 ZIP codes. How can I compare the two and remove all of the entries in A except those that match B. I would also like to have A sorted so that the identical entries line up with B. There may be entries in B that are not in A, so I would like A to contain a blank cell where the match would be. I used =IF(VLOOKUP(B1,A:A,1,FALSE),"MATCH","") to highlight the matching ZIP codes in A, but that does not remove the unwanted ZIP codes or sort them...

Replacing the Numbers
In my report I want to replace the value of my text data to a word. Example: in my report the TextBox: is (loc) which the "1", "2", "3" these are the records in the tables. They print out on the reports as 1, 2, and 3. I want to change that to read 1=ED, 2=EU and 3=EZ. And blank, if the user leave it blank as "NONE" I hope that's clear Thanks Check out your more recent thread with the same question. -- Duane Hookom MS Access MVP "bladelock" <bladelock@discussions.microsoft.com> wrote in message news:1FB81F68-1733-4174-8D...

Outlook 2002 Contact Phone Number Field Formatting
Why don't phone numbers entered in the phone number contact fields on Outlook 2002 automatically format to include the paren's and hyphens? If this is suppose to happen, what is the setting to make it happen? They do here and always have. What are you seeing? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer mike <michael.kelner@fmr.com> asked: | Why don't phone numbers entered in the phone number | contact fields on Outlook 2002 automatically format to | include th...

Unhiding Column A
I am using Excel 2003 SP2 and I have a spreadsheet with column A missing. I have followed all the Help Menu advice. I can navigate to column A and hit Format Column Unhide or Format Column Width, but to no avail. It is either hidden or set to zero width, but in any case, it will NOT show up. I have searched all the group postings, been to Microsoft.com help (yea, right) and done everything I can think of. Even installed a macro I found to unhide columns by macro. Any help would be most appreciated. John13 try selecting the cells button to the left of the column headers and on top o...

How to Merge Date from 3 columns to 1
I have a spreadsheet where the date is spread over 3 columns I,J & K and I wish to merge them to Column B in the format dd-mmm-yyyy as shown below. B I J K 02-Feb-2003 02 Feb 2003 27-Mar-1999 27 Mar 1999 01-Apr-1978 Apr 1978 01-Jan-1998 1998 14-Jun-2002 14 Jun 2002 Where there is a blank for the day in 'Column I' I need it to copy as 01 and where there is a blank for the month in 'Column J' I need it to copy as Jan. Where all 3 column...

Summarizing a Report
I have a database linked to an excel file for the table. The excel file contains the results for a school archery tournament. Each school is allowed to have 24 archers but only the top 12 count as a team score and 4 must be male and 4 must be female. My report works fine giving me the top 12 and a team total for each school and division, but it does not ensure the gender ratio is met. Any ideas?? Also, I would like to create a report that shows just the schools and team totals, but the only place I currently have team totals is in a report. -- Thanks, Cubbie You w...

SQL totalling items report
I will be very grateful if someone will give me the SQL to run a query using the Northwind data base that will produce a report that shows totals of “Units in Stock” for each “Category” (ie Beverages etc) that runs across the report so each column in headed by the Category (ie Beverages) and so on. Many thanks -- with kind regards Spike Take a look at Crosstab query. I don't have Northwind on this computer so I can't tell you the exact methodology, but the crosstab wizard may be able to help you. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop ...

Multiple reports with consecutive numbering
Is there a way to print out multiple reports and have consecutive numbering? I want to be able to print three or four different reports and have the pages numbered consecutively or maybe it is sequentially starting at page 1. I can get the the reports to print in order but just can't get the page numbering figured out. Thanks Mark Mark, I think you will need to use VBA to do this. The (untested!) skeleton of the idea would be like this... In a standard module, declare a global variable, maybe like this: Public StartingPageNumber As Integer On a suitable event, perhaps the ...

Moving ILC for Standard Items into Reorder Number field
I'm using the following commands to fill the Reorder Number field on Matrix Components with the ILC from the Matrix Parent and it works well... UPDATE SupplierList SET SupplierList.ReorderNumber = ItemClass.ItemLookupCode FROM SupplierList INNER JOIN ItemClassComponent ON SupplierList.ItemID = ItemClassComponent.ItemID LEFT JOIN ItemClass ON ItemClassComponent.ItemClassID = ItemClass.ID This leaves the Reorder Number field blank for all of my Standard items. What coding would I need to fill the Reorder Number field with the ILC for my Standard items? Thanks in advance! -- Kris R...

Pivot Table Columns
Is there any way to make the columns fill down in a pivot table? Some of the people seeing the reports get confused easily. In the example below is there any way to make the parent copy down on the next two rows? PARENT QUANTITY PO_NUMBER S00396 49 S07265-00 -------- S07272-00 -------- S07295-00 S00397L 62 S07265-00 Thanks Sharder -- sharder ------------------------------------------------------------------------ sharder's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25488 View this thread: http://www.excelforum.com/showthread.php?threadid=390185 I thi...

how to get word amounts from numbers in cels
I AM USING OFFICE 2003 EXEL, AND IN MY COLUMS WHEN I MAKE A SUM , THE RESULT OVIOUSLY COMES IN NUMBERS, MY QUESTION IS HOW CAN I OREDR EXEL TO WRITE, A NUMBER IN WORDS IN THE CEL BELOW TO IT Try searching Google with Excel numbers in words as the search criteria ... lots of advice and code. Some examples: http://www.meadinkent.co.uk/xlnumberstext.htm http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360 Please try not to use all capital letters ... it doesn't make it any easier to read. Regards Trevor "DONPITO" <DONPITO@discussions.microsoft.com> wrote...

Custom Entities dont appear to have a standard 'owner' field
Hi there, We are using CRM 4.0 and have created a few custom entities, but are not able to use any assigning options with them, as they have no 'owner' field. We've tried creating it manually (many to 1 relationship to the user field) but this doesn;t allow us to 'assign' the record at any stage. is this not possible or something i'm missing? THanks matt Matt; When a custom entity is created, one of the required fields is "Ownership". Your options are "User" or "Organization". "User" is the default, and if you choose i...

Custom number format always defaults last number to 0.
I am trying to format cells to input credit card numbers in Excell 2000. When I set up a custom number format for four sets of four numbers (####-####-####-####) the result always changes the last number to a 0. How do I overcome that? Excel only lets 15 digits be used in numbers To have the 16th number most people use text. "scubadave" wrote: > I am trying to format cells to input credit card numbers in Excell 2000. > When I set up a custom number format for four sets of four numbers > (####-####-####-####) the result always changes the last number to a 0. How...

Code for counting number of records in query
Hi, I need to count the number of records in queries quite a lot to check for duplicates or to ensure the data I'm about to append hasn't already been added & then inform the user. I've been using this code to get the count of records: Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQLcnt As String strSQLcnt = "SELECT Count(…………. etc Set db = CurrentDb() Set rs = db.OpenRecordset(strSQLcnt) xx = rs.Fields(0) rs.Close: Set rs = Nothing Set db = Nothing If xx = 0 Then etc Is there a simpler or shorter way...

type, but nothing appears and other wierdness
I've noticed a few wierd things. Sometimes in Excel, I try to type in some values, but nothing happens. Some keystrokes work, others do not. Then in Powerpoint, the keyboard on one document is French, even though it's selected as US. Other times, everything in Office is exceedingly slow. Restarting will get rid of the problems but temporarily. Should I trash the preferences? If so, how do I do this? Or should I remove Office and reinstall? Or is there something else I can try? I've got Office 2004 on a Powerbook G4. Thanks for any advice Carl That does sound like a lot of wei...

Help calculating totals
Hello! I am using Excel 2002. I have a spreadsheet that is about 300 rows long. Each row refers to a specific serial# of a copy machine. Column A defines what brand the copier is, and say Column B defines how many copies it made. Column B would be the "January" column, so then I would C,D,E. etc for the 12 months going across. I am looking for an easy way, so at the bottom of the spreadsheet, I can have a total for each of the months, for the # of copies that each of the different brands made. I am constantly adding and removing different serial#'s, so I'm not sure what the...

total the subtotals
I have a report that has sums for each cost item. For a particular scope of work, I have the labor, material, contracts totaled separately. I want to add a total of those. I added the box and it works somewhat. IF the items above ALL have a total (some items may not have contracts, just labor). Then I get a total of all those. =Sum([Labor])+Sum([Material])+Sum([Sub-Contract])+Sum([Other-Direct]) If one of those items is blank, the new total line I tried to create is blank. Seems all or nothing. Probably something easy, but I'm lost Thanks The problem is t...

2007- unwanted column chart n/a labels displaying
I am using Excel 2007 and have found that I cannot get the NA() labels to NOT display. As desired, there is no column where the cell values are na() but the labels for those cells are displaying as "N/A". How do I keep these labels from appearing? Interesting. I made a Line chart with =NA() and the points were joined (as expected) When I added labels, the #N/A did NOT appear A copy of the same charge was changed to a Column chart and the #N/A does appears But then, with a column chart you could just leave the cell blank. What type of chart are you using? best wishes -- Bernard...