How to sum cell numerical values based on text suffix
Requirement:
The user has a table with columns containing the following type of data:
| January |
| 1RB |
| 1RB |
| 1RB |
| 1RB |
| 2RB |
| 1RB |
| 1RB |
| 1PR |
| 2PR |
| 1FO |
The user requires a formula that can sum the numerical values based on the two digit suffix. For the data above, the following results would be expect:
| RB | 8 |
| PR | 3 |
| FO | 1 |
Solution:
Code:
=SUMPRODUCT((RIGHT($A$2:$A$11,2)="RB")*VALUE((LEFT($A$2:$A$11,1)))
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use SUMPRODUCT and COUNT question for unique associations |
| How to use SumProduct array formula |
| How to use SUMIF |
| How to combine LARGE and SUMIF - Array formula |
| How to use SUMPRODUCT |
| How to use IFERROR, ROUND and SUM in one formula |
| How to sum from two tables based on From-To date criteria |
| How to use SUMIF to extract attendance details from a register |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
