StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's my option - pretty similar to already posted, but anyway... especially if you're interested in learning, which is so rare today)</p> <p>Assuming you have your list starting A2, use the following array formula:</p> <pre><code>=SUM(LEN($A2)-LEN(SUBSTITUTE($A2,CHAR(ROW(INDIRECT(CODE("a")&":"&CODE("f")))),""))) </code></pre> <p>Just as reminder - press <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> instead of usual <kbd>ENTER</kbd>.</p> <p>Some explanations:</p> <ol> <li>Range of letters a-f is generated using char codes of range edges, transformed back to the array of chars using <code>CHAR(ROW(INDIRECT(...)))</code> structure.</li> <li>Then "nightmare" comes to help, finally summarizing the obtained numbers of substituted vs original string subtractions.</li> <li>Thus, in case of such double conversion you do not need codetable)))</li> </ol> <p>And two more similar "nuts" samples - just for learning purposes.</p> <p>In case you need to summarize all <strong>digits matches</strong> - you still may use the above using 0 and 9 as input (digits are chars with 48 to 57 codes starting 0). However, the following more simple solution will go as well:</p> <pre><code>=SUM(LEN($A2)-LEN(SUBSTITUTE($A2,ROW($1:$10)-1,""))) </code></pre> <p>The trick here is that we may generate numbers 0-9 using array or row numbers 1-10 <strong>minus</strong> 1 - <code>ROW(0)</code> would generate an error.</p> <p>Finally, if you need to calculate a <strong>sum of all digits</strong> in the string - use this:</p> <pre><code>=SUM(IFERROR(VALUE(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),0)) </code></pre> <p>Here we disintegrate the initial string to letters using <code>MID</code> for every single char, and then test it against being a number using <code>IFERROR</code> and returning 0 for anything but digit.</p> <p>Last 2 are (obviously) your beloved array nuts)))</p> <p>I use the above samples in my Excel training for QA stuff (by the way welcome to SE, colleague!), thus demonstrating typical functions / approaches for nuts cracking. Hope that was useful for you as well. However, all the previous answers deserve at least your fair upvote, especially @barry's nuts-free recipe)</p> <p>For your convenience sample file is shared: <a href="https://www.dropbox.com/s/qo5k479oyawkrzh/SumLettersCount.xlsx" rel="nofollow">https://www.dropbox.com/s/qo5k479oyawkrzh/SumLettersCount.xlsx</a></p> <p>Good luck with your testing)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload