excel不同的SUM.IF数组函数 [英] excel different SUM.IF array function
问题描述
我有一个非常具体的报告,无法更改其布局。基本上看起来像这样:
I have a very specific report, which I can't change the layout. Basicly, it looks like this:
A B C
1 ASD Other 10
2 ASD Other 10
3 ASD Other 10
4 JKL Other 20
5 EFG Other 5
6 EFG Other 5
7 GHJ Other 10
Ergo,行可以重复,当行重复时,该行的值(C)保持不变。
Ergo, lines CAN be repeated, and when they do, the value (C) stays the same for this line.
使用条件格式(= $ A2 <> $ A1),将重复值(仅C列)设置为白色。
Using conditional formatting, (=$A2<>$A1), the repeated values (C-column only) are made white.
如何对这些值求和,以便每条不同的线仅计算一次(应返回45)?
How can I do a SUM of these values, so that each distinct line will only be calculated once (Should return 45)?
提前,谢谢
PS:我正在使用Excel 2003
PS: I'm using Excel 2003
推荐答案
当我将其放入单元格 D9
并将其作为数组公式(Ctrl + Shift + Enter)输入时,它适用于您的情况。但请注意,第8行中有空白行可用于此工作:
When I put this in cell D9
and enter it as an array formula (Ctrl+Shift+Enter), it works for your scenario. But note that there's blank row in Row 8 for this to work:
=SUM(IF(B1:B7<>B2:B8,D1:D7,0))
这篇关于excel不同的SUM.IF数组函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!