Have excel return a number value based on words entered daily

miraclmoore

New Member
Joined
Feb 20, 2004
Messages
6
I have put together a spreadsheet that shows all of my associates on both shifts with all 7 days of the week. What i am doing is inputting their daily work status and i want to be able to get excel to return a number value for attendance reasons for each day of the week.

So these are the words i use:
Full Shift
Late In=1
Absent=2
Early Out=3
NC/NS=4

So if it's Tuesday and employee "A" is Absent when i type in Absent for that day i need the number 2 to automatically show up in a different column. If that same employee is late the next day and i type in Late In i need the number 1 to be added to the same column for a total of 3 points for the week so far.

Can anyone help me with this?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Does this give you what you need?

excel_summing_points.jpg


The formula is:

Code:
=M$3*COUNTIF(C3:I3,L$3)+M$4*COUNTIF(C3:I3,L$4)+M$5*COUNTIF(C3:I3,L$5)+M$6*COUNTIF(C3:I3,L$6)
 
Upvote 0
Does this give you what you need?

excel_summing_points.jpg


The formula is:

Code:
=M$3*COUNTIF(C3:I3,L$3)+M$4*COUNTIF(C3:I3,L$4)+M$5*COUNTIF(C3:I3,L$5)+M$6*COUNTIF(C3:I3,L$6)
That was EXACTLY what i needed! Thank You very much, i really have to expand my excel knowledge. There are so many things you can do with the program that i haven't had a chance to unlock!!!

Once again your a lifesaver!!!
 
Upvote 0
Cool. Glad it worked. I'm far from an an expert, but let me know if there's any questions about this one. Have fun.
 
Upvote 0
Does this give you what you need?

excel_summing_points.jpg


The formula is:

Code:
=M$3*COUNTIF(C3:I3,L$3)+M$4*COUNTIF(C3:I3,L$4)+M$5*COUNTIF(C3:I3,L$5)+M$6*COUNTIF(C3:I3,L$6)
Here's a different way to write that formula...

=SUMPRODUCT(SUMIF(L$3:L$6,C3:I3,M$3:M$6))
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

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