替代，忽略和动态范围的总和 [英] Sum with substitute, ignore and a dynamic range

问题描述

我用巴里·胡迪尼"给出的答案几乎可以解决我的问题但是我遇到了将其结合到我的需求中的问题.

I came pretty close to solving my problem with this answer given by "barry houdini" But I run into problems incorporating it to my needs.

 =SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))

编辑#3- @Rory 提供了很好的答案来解决我的问题.但是我没有想到的是，如果在单元格中输入非数字值，则公式将返回错误.因此，我真的很想调整以下公式以忽略字母"x" 或非数字值(如果更简单的话):

Edit #3 - @Rory came with a very good answer to solving my problem. But what I hadn't thought about was if you enter a non-numerical value inside a cell, then the formula returns an error. So I would really like to tweak the following formula to ignore the letter "x", or non-numerical values (if that's easier):

-

ActiveCell.FormulaR1C1 ="= SUMPRODUCT(VALUE(0& SUBSTITUTE(INDIRECT(""R10C:R [-1] C"，FALSE)，""s"，"")))"暗单元格范围对于范围内的每个单元格("all_cells")Cell.Formula = Range("one_cell").Formula下一个

我现在想做的是编写一个VBA脚本来总结动态范围，并包括后面带有"S"的值，但这些操作不会失败或在其他任何字母上返回错误.最重要的是字母"x".

What I'm now trying to do, is write a VBA script to summarize a dynamic range, AND include values that have an "S" behind them BUT not fail or return an error on any other letters. Most importantly the letter "x".

设置完第一个公式后，我想在右侧自动填充该公式；停在已知的列上.

After that first formula is set, I want to autofill that formula to the right ; stopping on a known column.

结束编辑

为了历史记录和其他Google搜索，这是我在遇到"s"和"x"问题之前使用的原始代码.

For the sake of history and other google searches, here's the original code I used before having the "s" and "x" problem.

ActiveCell.FormulaR1C1 = _
    "=SUM(INDIRECT(ADDRESS(10,COLUMN())&"":""&ADDRESS(ROW()-1,COLUMN())))"

这基本上是从设置的行(第10行)中进行汇总，并在具有此公式的单元格之前的行上停止汇总.

What this does is basically summarize from a set row (row#10) and stop the summary on the row just before the cell with this formula.

我要实现的示例:"3 + 4 S +2 + x = 9";而不是"= 5"或"##"

Example of what I want to achieve: "3+4S+2 + x = 9" ; instead of "= 5" or "##"

推荐答案

我终于解决了我的问题！我意识到嵌套替换公式可能是一个不错的解决方案，并且在经历了几次错误之后，我意识到该范围只需要提到一次，瞧！

I finally solved my problem! I realized nesting the substitute formulas could be a nifty solution, and after several errors I realized that the range only needed to be mentioned once, and voila!

ActiveCell.FormulaR1C1 ="= SUMPRODUCT(VALUE(0& SUBSTITUTE(0& SUBSTITUTE(INDIRECT(""R10C:R [-1] C"，FALSE)，""s"，"")，" x"，"")))"

这篇关于替代，忽略和动态范围的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！