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>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,629
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
35,542
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,089,314
Messages
5,407,519
Members
403,151
Latest member
floydschoice

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top