Excel Formula

M

Mark

I have a spreadsheet to log a persons manhours performed
for the month. 31 columns with a totals column to follow
the last day of the month.
We are a military organization and there are a number of
different statuses that a guy could be in on a given day.
If, on the 5th, the guy works a drill I want to record it
as a "U" so I know his status for the day. I want the
totals column to read that as 8 hrs worked. How can I get
my totals column to recognize an alpha character as a
number?
Mark
 
J

Jason Morin

Assuming alpha characters are recorded in row 1, try:

=SUMPRODUCT((A1:AE1="U")*8)

HTH
Jason
Atlanta, GA
 
D

David McRitchie

Hi Mark,
Good for upper or lowercase, each "U" counts as 8 units.
=SUM(A:A)+COUNTIF(A:A,"U")*8
 
Top