Summing numbers & text

  • Thread starter Thread starter Robert Mitchell
  • Start date Start date
R

Robert Mitchell

Being a total dweep in this area of Excel, your expertise would be greatly
appreciated.

I am using Excel 2002. I have a spreadsheet that lists names. Each column
after the name is the day of the month. In each cell there is a number or
text(e.g. 14, 15, 22, 27, K, U, BKM, etc.).

The thing I want to do is take the numbers and/or text and sum them so as
each day represents 8 hours and give me the total for the month.

Thank you,


Bobby Mitchell
 
How is your data laid out - is it something like the following:-??

A B C D E F G H I J K
1 Jan 12 13 15 22 25 27 K U BKM
2 Feb 10 14 17 24 26 27 G F BKM

I'm assuming there has to be gaps or something different, as other wise it would be as simple as
knowing there are 9 cells and just doing 9 * 8. In this case therefore, I am assuming your data
probably looks like either of the following scenarios:-

A B C D E F G H I J K
1 Jan 12 15 22 25 27 K U BKM
2 Feb 10 14 17 26 27 F BKM

or

A B C D E F G H I J K
1 Jan 12 13 22 25 K BKM
2 Feb 14 17 24 26 27 G F BKM

In either case, you could put a formula into Column K (Assuming the farthest you had data was out
to Col J), along the following lines:-

=COUNTA(B1:J1)*8

The COUNTA simply counts the cells that are not blank, which should do what you want regardless of
whether they are numbers or text, and will then simply multiply that count by 8. If this isn't
what you are after, then just post back with some clarifications.
 
Thanks for responding.

The layout is along these lines.

A B C D E F G H I J
K
1 Jones 17 22 27 24 BKM 20 21 18 K
2 Smith 18 22 19 21 19 24 27 25 18

Where there is nothing means it is a day off.

Appreciate your assistance.

Bobby
 
Your guidance works very well. I can get the total hours for the month with
what you gave me.

Is there some way to make a distinction between the entries made in the
cell. e.g. if I have a "K" or a "U" in that cell and want to count it
separately from the other entries how could I count them without including
the other data in that range? The "K" or "U" can occur on any given day.

Bobby Mitchell
 
=COUNTIF(B1:J1,"K") will count all instances of K and K only, being in the range B1:J1.
 
Excellent guidance. What you have given me saves me from beating my head
against the wall.

Your expertise is greatly appreciated.

Thank you,

Bobby
 
Back
Top