Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing part of a string excluding cells
Hi
Sorry, I hit enter on my last post before I finished! My problem is that in a column that I am trying to sum, most cells have the format c1.3 (1 letter and 3 digit spaces) but some cells simply have an "X". I tried to use the formula advised to Arturo: =SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or count them as 0. I assume this needs to be an "if_then_" format but I am stuck on how to do this? Cheers Soph |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing part of a string excluding cells
You could use a "real" array formula:
=SUM(IF(LEN(I9:I32)<4,0,--(RIGHT(I9:I32,3)))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) soph wrote: Hi Sorry, I hit enter on my last post before I finished! My problem is that in a column that I am trying to sum, most cells have the format c1.3 (1 letter and 3 digit spaces) but some cells simply have an "X". I tried to use the formula advised to Arturo: =SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or count them as 0. I assume this needs to be an "if_then_" format but I am stuck on how to do this? Cheers Soph -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing part of a string excluding cells
Thanks dave!!! This is perfect, really appreciate the help :o)
"Dave Peterson" wrote: You could use a "real" array formula: =SUM(IF(LEN(I9:I32)<4,0,--(RIGHT(I9:I32,3)))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) soph wrote: Hi Sorry, I hit enter on my last post before I finished! My problem is that in a column that I am trying to sum, most cells have the format c1.3 (1 letter and 3 digit spaces) but some cells simply have an "X". I tried to use the formula advised to Arturo: =SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or count them as 0. I assume this needs to be an "if_then_" format but I am stuck on how to do this? Cheers Soph -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells in every 14th row | Excel Discussion (Misc queries) | |||
How do I rotate Horazontial cells vertical, so part of the chart . | Excel Worksheet Functions | |||
Help adding text values | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Cells and and spaces after a string of text | Excel Worksheet Functions |