OzGrid

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

 

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



stars (0 Reviews)