I've been trying to get to grips with SUMIF & COUNTIF functions in Excel recently, with limited success. I've realised the crux of the problem is that help pages give far too specific examples, including the official Office support.
最近，我一直试图在Excel中处理SUMIF & COUNTIF函数，但收效甚微。我意识到问题的关键在于，帮助页面提供了太多具体的例子，包括官方办公室的支持。
I already know how to put together complex tests with multiple criteria already, using IF statements. What I really need is a guide to how to convert IF statements for use in such functions.
我已经知道如何使用IF语句将复杂的测试组合在一起。我真正需要的是如何转换IF语句以在此类函数中使用的指南。
The real issue for me is what happens to cell references? I have a column of cells, each with some value for a given property. With an IF function I can go into the adjacent column, test the neighbouring cell using some criterion or set of criteria to find its value for a given property, and return an appropriate answer. I click and drag my formula down to check all the cells.
对我来说，真正的问题是单元格引用发生了什么?我有一列单元格，每个单元格都有给定属性的值。使用IF函数，我可以进入邻列，使用一些标准或一组标准来测试邻单元格，以找到给定属性的值，并返回适当的答案。我点击并拖拽我的公式来检查所有的单元格。
Eg. A1:A10 are the cells I'm testing. The property I'm checking is their length, whose value will be the number of characters. The appropriate answer will be whether the number of characters is above or below a threshold.
如。A1:A10是我正在测试的细胞。我要检查的属性是它们的长度，其值将是字符的数量。适当的答案将是字符的数量是高于还是低于阈值。
Put together; IF(LEN(A1)>50,"above","below")
放在一起;如果(LEN(A1)> 50岁“以上”,“下面”)
Pasted into B1 and dragged down to B10 I get an array of answers.
粘贴到B1，拖到B10我得到了一系列答案。
Suppose I want to count all the cells which meet the condition, that's where I'm stumped. COUNTIF looks like I could just specify the range (A1:A10) and condition LEN(A1)>50 and get my answers. But what do I put inside LEN()? I want to go through and check for each cell in the range, how can I specify just one? Specifying none: LEN() or the range LEN(A1:A10) won't work.
假设我想要数一数所有满足条件的细胞，这就是我的难处。COUNTIF看起来像是我可以指定范围(A1:A10)和条件LEN(A1)>50并得到我的答案。但是我在LEN()里面放了什么?我要检查范围内的每个单元格，怎么才能只指定一个呢?指定none: LEN()或range LEN(A1:A10)不能工作。
For highlighting cells (conditional formatting), it's easy, just put the top left cell of the array, so LEN(A1), but that doesn't work either!
要突出显示单元格(条件格式)，这很容易，只需将数组的左上角单元格，即LEN(A1)，但这也不行!
I hope that's made the problem clear. Obviously I could just have IF(LEN(A1)>50,1,0) in B1:B10, and SUM(B1:B10) in C1 or something, thus counting all the cells which match the criteria in the if statement. But that seems like a totally retrograde step which negates the benefits of COUNTIF entirely. Namely that it saves space and reduces complexity in the sheet by doing away with intermediate steps.
我希望这个问题已经解决了。显然，我可以在B1中有IF(LEN(A1)>50 1,0) B10和C1中有SUM(B1:B10)这样就可以计算出所有符合IF语句条件的单元格。但这似乎是完全倒退的一步，完全否定了COUNTIF的好处。也就是说，它节省了空间，减少了表单中的复杂性。
And I have at least 1 sheet for which that definitely won't work owing to the volatile nature of my array sizes; I wouldn't be able to fit the additional intermediate columns if I wanted to!
因为数组大小的不稳定性，我至少有一张表是不行的;如果我想的话，我将无法满足额外的中间列!
SO, can any IF-statement-style check be converted to work with COUNTIF/SUMIF, if so then how, and are there any other tips you could include in case someone with a similar problem comes searching? Thanks so much for answers and help!
那么，是否可以将任何if -语句样式的检查转换为与COUNTIF/SUMIF一起工作，如果可以，那么如何进行?如果有类似问题的人来搜索，您是否可以包含其他提示?非常感谢您的回答和帮助!
1 个解决方案
#1
1
Use SUMPRODUCT:
使用SUMPRODUCT:
=SUMPRODUCT(1*(LEN(A1:A10)>50))
COUNTIF/SUMIF do not like it when you try to modify the range to be tested. You can do a lot with the criteria, but not the range. It is what is given up to use it in a non array form.
COUNTIF/SUMIF不喜欢您尝试修改要测试的范围。你可以对标准做很多，但不能对范围做很多。它是用来在非数组形式中使用的。
#1
1
Use SUMPRODUCT:
使用SUMPRODUCT:
=SUMPRODUCT(1*(LEN(A1:A10)>50))
COUNTIF/SUMIF do not like it when you try to modify the range to be tested. You can do a lot with the criteria, but not the range. It is what is given up to use it in a non array form.
COUNTIF/SUMIF不喜欢您尝试修改要测试的范围。你可以对标准做很多，但不能对范围做很多。它是用来在非数组形式中使用的。