Sum using right or left function

sneaky911

New Member
Joined
Feb 5, 2016
Messages
17
I have a simple attendance tracking spreadsheet. Data validation is set up to select the "violation" from a list. The violation also has a point total attached to it. Example: Call off 1.0, Early out 0.5, NCNS 3.0, etc. How can I sum a range using the "Right" function? I'm only interested in the values 1.0, 0.5, 3.0 etc.

I can get it to calculate if I do each cell individually:
=SUM(RIGHT(C3,3),RIGHT(D3,3),RIGHT(E3,3))

How can I simply highlight the entire date range and use the RIGHT function to calculate the point values? (Columns C-E)

NameTotal Points8/2/198/3/198/4/19
Bill3.00Early Out 0.5Absent 1.0NCNS 1.5
Cathy1.00Excused 0.0Early Out 0.5Early Out 0.5
Sally1.50Tardy 0.5Tardy 0.5Tardy 0.5
John2.00Vacation 0.0Absent 1.0> 50% shift worked 1.0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
=SUMPRODUCT(--(RIGHT(C2:E2,3)))
 
Upvote 0
Try:

=SUMPRODUCT(--RIGHT(C3:E3,3))

PS.... Just saw Fluff's similar response. There is no need for, nor benefit to, putting an extra set of parentheses around RIGHT(C3:E3,3).
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Need help with one more thing. I've modified your formula a bit. It works as expected with one exception...I need to include something that will avoid throwing an error if there's a blank cell in the range. I've tried a few different things all producing different results. Just to clarify the blank cell would be in E3:NX3.

=SUMPRODUCT(--(INT($E$2:$NX$2>TODAY()-365)),--(RIGHT(E3:NX3,3)))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top