Excel Magic Trick 982: Add w One Condition, Use SUMIF Instead of SUMPRODUCT or DSUM

Compare and contrast SUMIF, SUMPRODUCT and DSUM functions for adding with one condition:
1) SUMIF function
Faster calculating than DSUM and SUMPRODUCT.
No need to do special syntax to convert TUREs and FALSEs to 1s and 0s, like with SUMPRODUCT.
Can Copy formula down column, unlike DSUM.
2) SUMPRODUCT
SUMPRODUCT requires special syntax to convert TUREs and FALSEs to 1s and 0s.
SUMPRODUCT takes longer to calculate.
3) DSUM
DSUM has problem that the Criteria argument requires that you list Field Name each time.
DSUM takes longer to calculate.

See timing done to determine which is fastest in formula calculation time.
* Excel MVP, Charles Williams has a great web site and free code that is great for timing formulas:
Full articles about improving calculation time:

[excelisfun]

You are welcome! I am so happy to hear that you are the go to person at work!!

[excelisfun]

Cool! Thanks for checking! That helps me to confirm that the settings that I am using to create the videos is working out well!

[Laydjo]

So true. I used to fear spreadsheets but ExcelIsFun videos changed all that. Thanks so much Mike.

[Laydjo]

As usual. great video.Always waiting for your uploads every week. Because of them I got proficient in excel that it has become my top skill.At work, I am one of the two persons people go to about excel stuff especially formulas (some are so amazed about the how text can be taken out of a series of characters in a cell). There a lot of excel gurus out there but no one explains the excel the way you do, Thank you so much Mike for unselfishly sharing your knowledge with us.

[excelisfun]

The Slaying Excel Dragon book and DVD have the same content as the videos, however, it is presented in a systematic way, from begging to end: good for people who want to learn really Excel really well. They are available to Amazon.

[excelisfun]

That is too true :) Someone will check the spelling on the book, but the workbook and videos, I have no spell checker for those - I guess it will just have to be fodder for laughter...

[excelisfun]

Yes, I am sorry about that. I filmed the video at home during the holiday and cannot post the file until tomorrow (work computer has the program that allows me to access my web site). I posted an annotation at the beginning of the video say that I could not post until tomorrow.

[MrFukutwice]

Just wanna say thank you for uploading in 720P Quality

[excelisfun]

It always depends, but a lot of work goes into each one.

[AhmadMustafaMohmedMetwalley]

to Laydjo1

we all get fall in love with excel, and the thanks you so much ExcelIsFun..:)

[krn]

Thanks Mike. Purchased yr book on Amazon and looking forward to receiving.

[excelisfun]

I do not have a method other than YouTube. You should show your boss these and then ask, why would YouTube be blocked for content that can increase profit?

I do have a book and DVD: Slaying Excel Dragons.

[krn]

Ha. Make sure someone spell checks for you. You know how good a speller you are. :)

[excelisfun]

Do you mean the "Show More" area below the video? That should be not a problem.

[excelisfun]

You pre-paid? I hope I can actually finish it. It has been quite difficult for a non-writer like me...

[excelisfun]

MrFukutwice, Will you check this video:

Excel Magic Trick 981: 123.45M From Text To Number, 123, 450, 000 From Number to 123.45M

and tell me if you see it as 720P Quality on your computer?

[pacman]

awesome videos. How long do you need to prepare for these videos?

[krn]

Yes sir the array book. Thanks krn14242.

[andhotsauc]

Hi, I;m thinking about buying the book/dvd. Do they contain the same material as what is on the utube links?? btw, thanks alot for all this info sir! Very much appreciated. Didn't even know what excel was last week, now I'm above average and learning new things everyday!