Using SUM function to count hours BUT ignore Letters?

  • Thread starter Will H, England
  • Start date
W

Will H, England

Hi I use sum functions to total hours in rows and previously used coloured
cells for holidays, vacations and sick etc. (not ideal!) Ive started using
the IFCOUNT function to countr the number of holidays etc taken but the
problem I have now is the =SUM(cell:cell) formulas I have are giving errors

How can I make the SUM formulas ignore the letters?

thanks

Will.
 
E

Eduardo

Hi Will,
could you give an example of what you try to sum and the complete formula
you have
 
B

Bernard Liengme

If I enter numbers 1,2,3... in A1:A10 and use =SUM(A1:A10) I get answer 55
If I then replace the 5 and the 10 by text, I get the correct value of 40
with the SUM formula
Sounds like something else is amiss with your data
 
W

Will H, England

Hi Eduardo, sure...

This is part of a week-ending spreadsheet thats i put everyones hours into.
Having column-group headings of Mon-sun and under those headings I have
columns for Basic, 1.5 and 1.8 rate. Then into these columns I type the
number of hours each worker has done per rate.

Here is an example of one of the formulas that adds the BASIC hourly rates up.
=M38+P38+S38+V38+Y38

The problem I get is when one cell contains a letter like "H" or "S" or "V"
representing a holiday, sick or vacation taken. These letteres are counted by
a formula in another column using =countif(M38:Y38, "H") for the holdays
column etc.

Because of the presence of these letters in certain cells it gives me
"#ERROR!" as the result.

I need the formula to total the numerical values (hours) but ignore the
letters.

Thanks

Will
 
W

Will H, England

Sorry, I wasnt using the =sum function I was adding cell-references because I
need to add the cells of every second or third column.

sorry about that.
 
W

Will H, England

I should have used the =SUM function and seperated the cell references with
commas,

Works great now thanks guys! Apprecited all the inputm I wouldnt have seen
my mistake otherwise


Will
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top