Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: SUM Numbers & Text In Same Cell

  1. #1
    Join Date
    18th February 2006
    Posts
    62

    SUM Numbers & Text In Same Cell

    I have several cells I need to sum that are MB K size related. IE.....

    Circuits per Router....
    155 MEG
    85 MEG
    100 MEG
    768 K
    512 K
    76 MEG

    need to total these
    add up the MEG as 416MEG
    and the K 1.2MEG
    Total 417.2MEG

    does this make sense ???

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th February 2006
    Posts
    62

    Re: Add Numbers With Text In Same Cell

    Any Takers ?
    I am trying to search from left to first space, this will be the number, second
    search will be the text, either MB or KB how can I store the vars for multiple cells and two different items parsed ???

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,352

    Re: Add Numbers With Text In Same Cell

    Hi,

    =SUM(IF(ISNUMBER(SEARCH("k",A1:A6)),SUBSTITUTE(A1:A6," K","")+0))/1000+SUM(IF(ISNUMBER(SEARCH("meg",A1:A6)),SUBSTITUTE(A1:A6," MEG","")+0))

    To Enter the array formula hold down Ctrl and Shift while pushing Enter.

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,715

    Re: Add Numbers With Text In Same Cell

    Use Data>Text to Columns.. to split the numbers and text into 2 different columns then do a SUMIF like

    =SUMIF(B1:B20,"MEG",A1:A20)

    Or, give the 2 columns headings and use a simple Pivot Table

    Or

    =SUM(IF(MID(A1:A6,FIND(" ",A1:A6)+1,256)="MEG",VALUE(LEFT(A1:A6,FIND(" ",A1:A6)-1)),0))

    Where there are no blanks in the range A1:A6

    To Enter the array formula hold down Ctrl and Shift while pushing Enter.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Check If Cell Has Numbers Or Text
    By SolidSnake745 in forum EXCEL HELP
    Replies: 4
    Last Post: August 6th, 2008, 11:44
  2. Remove Text From Cell Housing Text & Numbers
    By iggiziggi in forum EXCEL HELP
    Replies: 3
    Last Post: February 29th, 2008, 11:29
  3. Cut Off Numbers From Cell Text
    By omnikron32 in forum EXCEL HELP
    Replies: 7
    Last Post: April 11th, 2007, 16:55
  4. Match (text) Numbers To Numbers From One Cell To Another
    By scratchmaster in forum EXCEL HELP
    Replies: 4
    Last Post: October 12th, 2006, 23:40
  5. making text or numbers fit in a cell
    By XLL in forum EXCEL HELP
    Replies: 1
    Last Post: March 1st, 2006, 00:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno