+ Reply to Thread
Results 1 to 5 of 5

Question: Summing the length of all cells in a range with Criteria in another range

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    California, United States
    MS-Off Ver
    Excel 2013
    Posts
    7

    Question: Summing the length of all cells in a range with Criteria in another range

    That is the best way I can describe it.

    The first part I have working (summing the length of all cells in a range) using an array formula.

    =SUM(LEN(a5:a73)-LEN(SUBSTITUTE(a5:a73,"x","")))

    (make sure to use Ctrl+Shift+Enter for the array formula)

    In the above example I have column "A" with a list of values with one or many "x"s.

    But now I need to sum only the rows where an "x" is in Column B. I'm not sure if it is a countif or lookup or match combination with an array formula.

    I've been searching and testing for hours finally broke down and registered to see if some of you gurus can help.

    Thank you in advance for your help.

    Here is an attached example:
    excel-question.xlsx
    Last edited by rtiger; 07-25-2013 at 01:19 PM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: Question: Summing the length of all cells in a range with Criteria in another range

    Whether X is an Instring of Column B cells? or Just X in cells?

    If possible please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    California, United States
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Question: Summing the length of all cells in a range with Criteria in another range

    Thank you for the reply.
    I attached the sample document.

    The SUM column (column D in the attachment) has cells with one or many "x"s as text.

    The array formula I have will add up all of those "x"s in all of the cells to get a total count at the bottom.

    In the attachment I have a column B also with a single "x" as text in some of the rows.

    So I need to do the same function of summing all of the "x"s in column D but only for those rows that also have an "x" in column B.

    I hope that makes sense. Thank you for the help.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: Question: Summing the length of all cells in a range with Criteria in another range

    Try this Non Array Formula...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    California, United States
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Question: Summing the length of all cells in a range with Criteria in another range

    I don't know how this works or what it is doing but it works. You are awesome. Thank 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. Count a Range of cells if another range of cells meets criteria
    By mjg060468 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-21-2013, 02:57 PM
  2. [SOLVED] CountIF for dynamic length column using criteria of Named Range
    By saber0091 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2013, 02:04 PM
  3. Replies: 6
    Last Post: 11-11-2012, 12:40 AM
  4. [SOLVED] Count a range of cells if another range meets criteria
    By LadySetsuka in forum Excel General
    Replies: 8
    Last Post: 04-14-2012, 10:09 AM
  5. Replies: 6
    Last Post: 01-30-2008, 01:30 PM

Tags for this Thread

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