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

1. ## 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.

Here is an attached example:
excel-question.xlsx

2. ## 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.).

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

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. ## Re: Question: Summing the length of all cells in a range with Criteria in another range

Try this Non Array Formula...

Formula:
`Please Login or Register  to view this content.`

5. ## 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!

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