Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
Hi Will,
could you give an example of what you try to sum and the complete formula you have "Will H, England" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Will H, England" <Will H, wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
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 -- __________________________________ HTH Bob "Will H, England" <Will H, wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
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 "Eduardo" wrote: Hi Will, could you give an example of what you try to sum and the complete formula you have "Will H, England" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM function to count hours BUT ignore Letters?
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. "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Will H, England" <Will H, wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Figured my mistake!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function and ignore if | Excel Worksheet Functions | |||
Count letters | Excel Discussion (Misc queries) | |||
How do i ignore letters in a formula? | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count letters | Excel Discussion (Misc queries) |