Formula for summing addresses within a single cell
I need a formula that will sum the number of addresses listed in a cell. An
example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. |
Formula for summing addresses within a single cell
BW wrote:
I need a formula that will sum the number of addresses listed in a cell. An example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. If it will suffice to count the number of commas plus 1 you can use this formula: =1+SUMPRODUCT(--(MID(A1,COLUMN(A:IV),1)=",")) Or this array* formula: =1+SUM(IF(MID(A1,COLUMN(A:IV),1)=",",1)) *Commit the array formula by pressing Ctrl+Shift+Enter, do not just press Enter or Tab. |
Formula for summing addresses within a single cell
Hi,
Assuming that all addresses are separated by commas (and there are no other commas), use the following. Cell B25 holds the addresses. =(LEN(B25)-LEN(SUBSTITUTE(B25,",","")))+1 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "BW" wrote in message ... I need a formula that will sum the number of addresses listed in a cell. An example of how they are entered is: 2550 Elm, 100 Main, 475 Maple, 702 Twin Oaks There could be any number from 1 to 50 addresses in any given cell. I'm using Excel 2003. |
All times are GMT +1. The time now is 05:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com