How to sum cell numerical values based on text suffix

< Back to Search results

 Category: [Excel]  Demo Available 

How to sum cell numerical values based on text suffix




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 |






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.


stars (0 Reviews)