Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

SUM Numbers & Text In Same Cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 ???

  • #2
    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 ???

    Comment


    • #3
      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.
      Kris

      ExcelFox

      Comment


      • #4
        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.

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X