Excel Magic Trick #203: SUMIF function formula 21 Examples

[ExcelIsFun]

@37no37 , please be more respectful of this site and not spam it with the same question on so many videos.

[madmommathmetician]

is there a way to "sumif" when a cell contains a certain text clause - cell may or may not start with said text

[ExcelIsFun]

@madmommathmetician , yes, if you wanted to sum numbers for just the text that start with "pre", try:

=SUMIF(RangeWithCriteria,"pre*­", SumRange)

[ExcelIsFun]

@madmommathmetician , * asterix (multiplication symbol) is the wild card. Try this video:

Excel Magic Trick 615: Adding With Approximate Criteria SUMIF Function and * Wildcard

[Lucillyloo]

Oh, you've made my colleague's spreadsheet so much simpler. Thanks!

[ExcelIsFun]

@Lucillyloo , you are welcome!

[jonathan92591]

Once again, I ran into a problem in Excel and was able to find a solution by watching your training videos. Thanks again dude!

[ExcelIsFun]

You are welcome!

[JWT0225]

Is there a way to produce the sum of the 5 lowest numbers in a column?

[ExcelIsFun]

Yes, if your values were in A1:A100, try the formula:

=SUM(LARGE(A1:A100,{1,2,3,4,5}­))

or if you have Excel 2007 and you want a message when 5 values have not been entered into the range, try:

=IFERROR(SUM(LARGE(A1:A100,{1,­2,3,4,5})),"Not Five Values, Yet")

[Andybelarus]

so u wrote a book on Excel tricks? great!

[ExcelIsFun]

Yes, it is $19 at Amazon (550 pages). Next week the DVD will be at mrexcel[dot]com and will have 53 videos and the e-book for $39.

[StoiankaJJ]

The magic trick helped me a lot! Thanks!

The only one I don't understand is when you use it between two values - less than 30 but greater than 20. The formula you use is =SUMIF(s;F26)-SUMIF(s;E26). BUT in the table with the input data the numbers that meet the criteria 20<x<30 are only 26, 22 and 23. How can their sum be 111? Pls, help!

[ExcelIsFun]

The formula for 'Less Than 30 And Greater Than Or Equal To 20' (which is what I was doing) should equate to 111, not 71. I have verified this with four different methods.

What you have is 20<x<30 . I have 20<=x<30 - Since the 20 is included in the "between interval", you use "x<20" AND "x<30".

[StoiankaJJ]

Thanks! Then what would be the formula in which we want the result to show the sum of numbers between 20 and 30, but not including either 20 or 30? Thanks again.

[ExcelIsFun]

For between, the criterion in cell E26 should be <=20 and the criterion in cell F26 should be <=30 and the formula in cell E26 should be =SUMIF(s,F26)-SUMIF(s,E26)

[StoiankaJJ]

Thanks again!

[ExcelIsFun]

I am glad that you like it!

[shemeymey]

These are awesome videos! I appreciate these all very much.^_^ .. you are really awesome with your lessons, so very easy to follow .. before seeing this .. my brain was burning!..LOL

[ExcelIsFun]

I am glad that they helped!

[shanees001]

Thanks for showing this video with 21 examples..this will help in my daily work.

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

If you exclude the 20s in the SUM you get 71. Adjust your formula to include the 20s and you will get the 111.

[ExcelIsFun]

The formula for 'Less Than 30 And Greater Than Or Equal To 20' sould equate to 111, not 71. I have verified this with four different methods. What formula are you using?

[Geotubest]

ExcelIsFun, I really love your enthusiasm. Thanks for helping me survive.

[ExcelIsFun]

You are welcome!

[Mafia2786]

I want my "SUMIF" formula to calculate a total from two columns eg. column B2:B70 and C2:C70 I tried the following =SUMIF(A2:A70,"Alex",B2:C70) but this only gives me the total of the first column B2:B70 and ignores C2:C70 ... Please Help =(

[ExcelIsFun]

You might ry a formula construction like SUMIF() + SUMIF()

where each SUMIF looks at a different column

[Mafia2786]

i've done

"=SUMIF(G2:G79,"Alex",H2:H79)+­SUMIF(G2:G79,"Alex",H2:H79)" is this what you mean ?

Thanks for the help and i'm sorry i'm kinda new too excel. Thanks again

[Mafia2786]

I've got it too work THANK YOU!!!!!

[mike22925]

your'e a great teacher, thanks, this series is fab

[ExcelIsFun]

You are welcome!!

[simabah]

Words can not express how happy I am to have found you. You are awsome! I truly think you are the best instructor that has ever lived in the whole world. I love to learn more and more so please keep it going. I always wanted to learn excel but was scared but now I am learning so much and I am not scared of excel anymore. Thanks a million!

[ExcelIsFun]

You are welcome!

[ucrmm1]

what a nice guy, thanks for the help! truly appreciate it.

[ExcelIsFun]

Dear ucrmm1 ,

I am glad it was helpful!

--excelisfun

[marizmendi1656]

Your a genius, thank s for sharing...

[ExcelIsFun]

Dear marizmendi1656,

You are welcome! i am happy to share, but I am no genius, juts a guy having fun with Excel!

--excelisfun

[ExcelIsFun]

Dear trunkhustler ,

I'll make a video and post it on Friday.

--excelisfun

[ka4irga]

Thanks doude! I will watching your tutorials all night and from morning go to exam ! ;]

[ExcelIsFun]

Dear ka4irga ,

I am glad that the videos help!!

--excelisfun