+ Reply to Thread
Results 1 to 6 of 6

Total formula from different cells

  1. #1
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,667

    Total formula from different cells

    Hi,
    See attchment. I need a formula on C1 that will total that contain "sp" from all different cells.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Total formula from different cells

    1. Please take a moment to check your Excel product/version and update your profile.

    2. Try:

    =SUMPRODUCT(--ISNUMBER(SEARCH("sp",B5:K14)))

    3. Adjust the range (red) to suit your real data.
    Attached Images Attached Images
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,667

    Re: Total formula from different cells

    Hi Glenn,

    I am so sorry... I meant the total of numbers that contains with sp.
    1sp + 2sp = 3.

    On the sample attachment, the total should be 39?

    Sorry and thanks in advance.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Total formula from different cells

    You have not answered my question about Excel Product/version.

    =SUM(IF(ISTEXT(B5:J14),--SUBSTITUTE(UPPER(B5:J14)," SP",""),0))

    should do it. In your (mystery) version of Excel, this may/may not be an array formula, which needs CTRL-SHIFT-ENTER to get it working.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-05-2014
    Location
    CALIFORNIA
    MS-Off Ver
    2010
    Posts
    1,667

    Re: Total formula from different cells

    Hi Glenn,

    It's windows 10.

    It worked. Thank you so much.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Total formula from different cells

    Your OS is irrelevant. Its the Excel PRODUCT that may eeed updating.

    Anyway - it works for you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2020, 06:30 AM
  2. [SOLVED] Help with formula needed for calculating total VAT of cells
    By jimmmi in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-15-2015, 03:30 PM
  3. Formula to total amounts in different cells
    By Treecko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-03-2014, 08:00 AM
  4. how to get the total of cells if it has a formula
    By hunhnee18 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-18-2012, 03:21 PM
  5. IF formula to make different cells total
    By AlmostAGeek in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-05-2009, 06:54 PM
  6. formula for total of cells which contain numbers greater than zero
    By william39 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2009, 06:36 AM
  7. [SOLVED] highlight cells to obtain total without using formula?
    By rdavia in forum Excel General
    Replies: 2
    Last Post: 01-18-2005, 02:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1