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>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
How about
=SUMPRODUCT(--(RIGHT(C2:E2,3)))
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,518
Office Version
2010
Platform
Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

sneaky911

New Member
Joined
Feb 5, 2016
Messages
17
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)))
 

Forum statistics

Threads
1,077,828
Messages
5,336,622
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top