How to match and sum a range where last two digits end with

[Quisp]

Hello
Im attaching a simplified example of what Im trying to achieve.
In the example spreadsheet, theres a range (B5:B10), with corresponding $ values in coiumn E. I want to know what the total $ sum is for each value in B5:B10 (Based on their last two seg values ending in 7x). What would be the best way to achieve this?

[AliGW]

In D14 copied down:

=SUMPRODUCT((--RIGHT($B$5:$B$10,2)=B14)*$E$5:$E$10)

[Glenn Kennedy]

=SUMPRODUCT((RIGHT($B$5:$B$10,2)+0=B14)*$E$5:$E$10)

copied down.

[Quisp]

Thank you both so much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Quisp]

Thank you I will def do so. Im trying to figure out why Im getting a #VALUE!
Ok, I was extending my range down the column to blank cells, which was giving me the error. Fixed now. Thank you

[Quisp]

Actually, since the range in the example will likely expand(rows added with data), is there a way to adjust the formula so that I dont have to revise the formula for new row additions to (B5:B10)(E5:E10)?Also, how would the formula be adjusted to include a blank cell, without giving me #VALUE?

[AliGW]

Try this:

=SUMPRODUCT((--RIGHT($B$5:$B$1000,2)=B14)*($B$5:$B$1000<>"")*$E$5:$E$1000)