|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 25th, 2006, 02:54 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum in numbers for text values
Help! I have been asked by work to create a spreadsheet for tracking the number of companies we are speaking to. I need to calculate the total number of companies in a certain column but some companies are listed twice as we have different contacts in each company. Do I need to somehow convert the text in to a numerical value in order to total each column and how do I go about excluding duplicates? Thanks Andy -- asgh77 ------------------------------------------------------------------------ asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700 View this thread: http://www.excelforum.com/showthread...hreadid=564707 
|
|
#2
July 25th, 2006, 03:25 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sum in numbers for text values
Andy,
The array formula (entered using Ctrl-Shift-Enter) =SUM(IF(A1:A1000"",1/COUNTIF(A1:A1000,A1:A1000))) will count the number of unique entries in A1:A1000. HTH, Bernie MS Excel MVP "asgh77" wrote in message ... Help! I have been asked by work to create a spreadsheet for tracking the number of companies we are speaking to. I need to calculate the total number of companies in a certain column but some companies are listed twice as we have different contacts in each company. Do I need to somehow convert the text in to a numerical value in order to total each column and how do I go about excluding duplicates? Thanks Andy -- asgh77 ------------------------------------------------------------------------ asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700 View this thread: http://www.excelforum.com/showthread...hreadid=564707
|
|
#3
July 25th, 2006, 03:38 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sum in numbers for text values
Hi asgh77 -
try Data / Subtotals - I ended up with the column below showing each value and how many, I then did a bog standard count at the bottom (does not count text values). any any any anyCount 3 fred fred Count 1 gowe gowe Count 1 jowe jowe Count 1 hopt hopt Count 1 5 Hope this helps --- Dika "asgh77" wrote: Help! I have been asked by work to create a spreadsheet for tracking the number of companies we are speaking to. I need to calculate the total number of companies in a certain column but some companies are listed twice as we have different contacts in each company. Do I need to somehow convert the text in to a numerical value in order to total each column and how do I go about excluding duplicates? Thanks Andy -- asgh77 ------------------------------------------------------------------------ asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700 View this thread: http://www.excelforum.com/showthread...hreadid=564707
|
|
#4
July 25th, 2006, 03:46 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sum in numbers for text values
Neat - never thought of using that trick
 Steve On Tue, 25 Jul 2006 15:25:34 +0100, Bernie Deitrick deitbe consumer dot org wrote: Andy, The array formula (entered using Ctrl-Shift-Enter) =SUM(IF(A1:A1000"",1/COUNTIF(A1:A1000,A1:A1000))) will count the number of unique entries in A1:A1000. HTH, Bernie MS Excel MVP "asgh77" wrote in message ... Help! I have been asked by work to create a spreadsheet for tracking the number of companies we are speaking to. I need to calculate the total number of companies in a certain column but some companies are listed twice as we have different contacts in each company. Do I need to somehow convert the text in to a numerical value in order to total each column and how do I go about excluding duplicates? Thanks Andy
|
|
#5
July 25th, 2006, 03:49 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Sum in numbers for text values
Thanks Bernie - that worked perfectly! Regards Andy -- asgh77 ------------------------------------------------------------------------ asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700 View this thread: http://www.excelforum.com/showthread...hreadid=564707
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I uniformly change the values (numbers) of many cells? Exce | Dave6 | Worksheet Functions | 1 | July 19th, 2006 11:40 PM |
| can numbers be assigned to values, replacing values for numbers | Cossloffe | General Discussion | 2 | June 3rd, 2006 10:22 AM |
| Why are 1/2 my numbers imported as text and the rest as numbers? | KBear | General Discussion | 2 | April 21st, 2006 01:40 PM |
| Generate series of numbers | [email protected] | Using Forms | 6 | November 17th, 2005 07:07 PM |
| How to calculate values from 2 tables | Luke | Running & Setting Up Queries | 2 | December 9th, 2004 03:55 PM |
Linear Mode
