如何在SUMIF中使用不连续的范围 [英] How to use discontinuous range in SUMIF

问题描述

我正在学习在excel中使用SUMIF。通常在这个公式的第一个参数是一个连续的范围（像这样的A1：F1）。但是现在我真的需要这样选择：A1，C1，E1由于数据集格式。我可以在Excel中做这样的事吗？甚至VBA。我也试图使用INDIRECT，但它只允许我填写一行。如果我自动填充下来，行号不会改变。以下是我使用的公式：

  SUM（（SUMIF（INDIRECT（{A1，C1，E1 ， G1， I1， K1， M1， 01， Q1， S1}）， < &安培; V1，INDIRECT（{ B1， D1， F1，H1，J1，L1，N1，P1，R1，T1}））））
  

任何人都知道如何为整个列执行此操作？感谢提前!!!

解决方案

使用不连续的范围（也称为多区域范围） / p>

但不行。

是的，你可以创建一个不连贯的范围使用范围语法 SUMIF（（A1，C1，E1，G1），...）一个命名范围（选择单元格，然后插入 - > range-> define） ， UNION（）函数或 INDIRECT（），但都不会工作，因为 SUMIF（）不支持不连续的范围。

所以你必须作弊。您的问题之前已经被问过，正如我通过搜索sumif discontinuous发现的：

http://www.mrexcel.com/forum/excel-questions/226429-sumif-discontinuous-cells.html

适应Oaktree的答案我们得到：

  = SUMPRODUCT（ - （MOD （COLUMN（$ A1：$ S1），2）= 1）， - （$ A1：$ S1> V1），$ B1：$ T1）
  

它是如何工作的？它创建三个列表，并找到每个列表中每个元素的值，然后将它们相乘在一起。奇数列的第一个参数为1，偶数列的第一个参数为0。第二个参数是1，如果它大于V1，否则为0.第三个参数是值一列。

I'm learning to use SUMIF in excel. Usually in the first argument in this formula is a continuous range (something like this A1:F1). But now I really need to choose something like this: A1, C1, E1 due to the data set format. Can I do somthing like this in Excel? or even VBA. I also tried to use INDIRECT but it only allows me to fill in one row. If I do an autofill down the row number won't be changed. Here is the formula I used:

   SUM((SUMIF(INDIRECT({"A1","C1","E1","G1","I1","K1","M1","O1","Q1","S1"}),"<"&V1,INDIRECT({"B1","D1","F1","H1","J1","L1","N1","P1","R1","T1"}))))

Anyone know how to do this for the whole column? Thanks in advance!!!

解决方案

Clever idea to use a discontinuous range (also known as a multi-area range).

But it won't work.

Yes, you can create a dicontinuous range using range syntax SUMIF((A1,C1,E1,G1),...) a named range (select the cells and then insert->range->define), the UNION() function, or INDIRECT(), but none of them will work, because SUMIF() doesn't support discontinuous ranges.

So you have to cheat. Your question has been asked before, as I found by googling "sumif discontinuous":

http://www.mrexcel.com/forum/excel-questions/226429-sumif-discontinuous-cells.html

Adapting Oaktree's answer we get:

=SUMPRODUCT(--(MOD(COLUMN($A1:$S1),2)=1),--($A1:$S1>V1),$B1:$T1)

How does it work? It creates three lists, and finds the value of each element in each list, then multiplies them together. The first parameter is 1 for the odd columns and 0 for the even columns. The second parameter is 1 if it's greater than V1, otherwise 0. The third parameter is the values one column over.

这篇关于如何在SUMIF中使用不连续的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！