Excel: Applying SUMIF to a range of values?

excel function sumifs
user3026602 picture user3026602 · Nov 24, 2013 · Viewed 41.8k times · Source

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

Mr. Polywhirl picture Mr. Polywhirl · Nov 24, 2013

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:

enter image description here

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.

enter image description here

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/

Related questions

How to return a result from a VBA function

How do I return a result from a function? For example: Public Function test() As Integer return 1 End Function This gives a compile error. How do I make this function return an integer?

Read More
What is the equivalent of "!=" in Excel VBA?

The problem is that != does not work as a function in excel vba. I want to be able to use If strTest != "" Then instead of If strTest = "" Then Is there another approach to do this besides !=? My function to mimic != …

Read More
Help needed with Median If in Excel

I need to return a median of only a certain category on a spread sheet. Example Below Airline 5 Auto 20 Auto 3 Bike 12 Airline 12 Airline 39 ect. How can I write a formula to only return a median value of the Airline Categories. …

Read More