MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dsum vs sumif


Posted by Lars on October 25, 2001 10:54 AM

Is there an advantage of using Dsum over sumif?

Thanks


Posted by Bob Umlas on October 25, 2001 11:30 AM

------------------------------------------
SUMIF can't do complex criteria, DSUM can;
DSUM requires data to be vertical; SUMIF doesn't.
Both are just tools to do a type of job. There are others in the mix, like SUMPRODUCT, array-entered SUM((...)*(...)) formulas, & on & on.

Posted by Mark W. on October 25, 2001 11:31 AM

DSUM permits complex criteria and can evaluate
multiple columns. SUMIF can be used on ranges
without an established data list (with its
required column labels) and a defined criteria
range.

Posted by Aladin Akyurek on October 25, 2001 11:54 AM

...there's no need to format the cell at all...
just enter your credit card number with hypens
(or spaces).

Posted by Aladin Akyurek on October 25, 2001 12:02 PM

Bob & Mark: "Is there an advantage of using Dsum over sumif"

when both are equally applicable, especially regarding performance?

I think Lars would be interested, at least I'm.

Aladin

Posted by Mark W. on October 25, 2001 12:17 PM

Re: Bob & Mark: "Is there an advantage of using Dsum over sumif"

I don't know for sure, but (if all things are
equal) I suspect that SUMIF may be faster. I
know that a Dfunction's criteria range is
manipulated by Excel in the background (e.g.,
when using =EXACT(A2,$D$1) in a computed
criteria the cell reference, A2, is incremented
to traverse the data set), and I suspect that
there might a fair amount of overhead associated
with this. Of course, one doesn't need to use
a computed criteria in the case described above.
I just used it as an example to illustrate that
there may be more going on in a criteria rane than
what meets the eye.

Posted by Anon on October 26, 2001 2:52 AM

Re: Bob & Mark: "Is there an advantage of using Dsum over sumif"

I suppose the way to find out is to do a timed test.
My experience of Excel is that actual tests to compare the time to process different ways of achieving the same result quite often don't produce the results that are expected.