Excel: Applying SUMIF to a range of values?

Question

I want to get the sum of values represented by 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.

Answer 1

You want to use 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.

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()

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.

Now sum up all the columns.

Here is the file completed: 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/

Answer 2

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

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

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

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