Excel：将SUMIF应用于一系列值？

I want to get the sum of values represented by 1,2,3,4

我想得到1,2,3,4所代表的值的总和

eg: =SUMIF(D5:D23,"1",G5:G23)+SUMIF(D5:D23,"2",G5:G23)+SUMIF(D5:D23,"3",G5:G23)+SUMIF(D5:D23,"4",G5:G23)

How can I do this operation? Please help me.

我该怎么做这个操作?请帮我。

2 个解决方案

#1

You want to use SUMIFS:

您想使用SUMIFS:

=SUMIFS(G5:G23,D5:D23,">0",D5:D23,"<5")

You want to sum all values from G5:G23 where the value from D5:D23 is greater than 0 and less than 5. So you require 2 criteria. >0 and <5.

您想要对G5:G23中的所有值求和,其中D5:D23的值大于0且小于5.因此,您需要2个条件。 > 0和<5。

Here is the function:

这是功能:

SUMIFS(sum_range,criteria_range1,criteria1,sum_range,[criteria_range2,criteria2],...)

Example:

---

Edit

Is think you want a vertical lookup - VLOOKUP()

认为你想要一个垂直查找 - VLOOKUP()

Formulas:

• G6: =VALUE(VLOOKUP(D6,$B$25:$E$30,2,FALSE))
• H6: =IFERROR(IF(SEARCH("Valve",VLOOKUP(D6,$B$25:$E$30,3,FALSE))>0,$D$23,0),0)
• I6: =E6*$D$22
• J6: =SUM(G6:I6)

Now select G6:J6, grab the handle at the bottom right, and drag to fill rows G7:J7 to G20:J20.

现在选择G6:J6,抓住右下角的手柄,然后拖动以填充行G7:J7到G20:J20。

Now sum up all the columns.

现在总结所有列。

Here is the file completed: http://www.filedropper.com/wpmccardiocosts_1

这是完成的文件:http://www.filedropper.com/wpmccardiocosts_1

You should check this out, it is very handy: http://www.mbaexcel.com/excel/tutorial-how-to-decide-which-excel-lookup-formula-to-use/

你应该检查一下,它非常方便:http://www.mbaexcel.com/excel/tutorial-how-to-decide-which-excel-lookup-formula-to-use/

#2

It may be simpler to use an array entered formula using SUM and IF:

使用SUM和IF使用输入数组的数组可能更简单:

=SUM(IF(D3:D23={1,2,3,4},G3:G23)) 

entered using [ctrl]+[shift]+[enter] (an array formula)

使用[ctrl] + [shift] + [enter](数组公式)输入

This will allow for use of many variables in place of 1,2,3,4 such as not continuous numbers, text ect.

这将允许使用许多变量代替1,2,3,4,例如不连续数字,文本等。