Using SUM function to count hours BUT ignore Letters?

[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.

 

[Eduardo]

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

 

[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

 

[Bob Phillips]

What IFCOUNT function, I don't know this? I assume that you mean COUNTIF,
but counting coloured cells is not a built-in function. See
http://www.xldynamic.com/source/xld.ColourCounter.html

 

[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

 

[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.

 

[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