如何在SUMIF中使用不连续的范围 [英] How to use discontinuous range in SUMIF
问题描述
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屋!