Conditional Sum Wizard Versus SUMIFS

[mark5767]

I recently started using the Conditional Sum Wizard in Excel 2000 (the version I use at home) and it appears to be very useful for adding up specific ranges in tables. I am also using Excel 2007 (version I use at the office) where I have the SUMIFS function available.

The two approaches appear to achieve similar good results when creating conditional sum formulas. However the SUMIFS formulas are a little more clear to me conceptually. The nested IF statements generated by the Conditional Sum Wizard can get a little confusing.

Does anyone have thoughts or comments regarding the advantages/drawbacks of using these two approaches? Again they both seem to work, I'm just trying to get feel for which approach I should use more often, especially in Excel 2007 where I have the SUMIFS available and it appears to be a "simpler" approach to conditional summing.

Thank you!

 

[T. Valko]

I recently started using the Conditional Sum Wizard in Excel 2000 (the version I use at home) and it appears to be very useful for adding up specific ranges in tables. I am also using Excel 2007 (version I use at the office) where I have the SUMIFS function available.

The two approaches appear to achieve similar good results when creating conditional sum formulas. However the SUMIFS formulas are a little more clear to me conceptually. The nested IF statements generated by the Conditional Sum Wizard can get a little confusing.

Does anyone have thoughts or comments regarding the advantages/drawbacks of using these two approaches? Again they both seem to work, I'm just trying to get feel for which approach I should use more often, especially in Excel 2007 where I have the SUMIFS available and it appears to be a "simpler" approach to conditional summing.

Thank you!

The SUMIFS function available in Excel 2007 and later is a better choice up to a point.

It's limited to doing only "straight comparisons". You can't "manipulate" an array like you can in an array formula.

For example, you can't do this with SUMIFS but it's simple with an array formula.

Column A = dates (all within the same year)
Column B = Yes or No
Column C = values to sum

Sum column C where the dates occur in the month of August and where column B = Yes.

Array entered:

=SUM(IF(MONTH(A1:A10)=8,IF(B1:B10="Yes",C1:C10)))

In this array formula we're "manipulating" the date array to test it for the month. You can't do that directly with the SUMIFS function.

For the SUMIFS, you'd have to set a specifc date range as the criteria, something like this:

Sum column C where the dates fall within 8/1/2011 to 8/31/2011 and where column B = Yes.

=SUMIFS(C1:C10,A1:A10,">="&DATE(2011,8,1),A1:A10,"<="&DATE(2011,8,31),B1:B10,"yes")

You can use cells to hold the criteria:

E1 = 8/1/2011
F1 = 8/31/2011
G1 = Yes

=SUMIFS(C1:C10,A1:A10,">="&E1,A1:A10,"<="&F1,B1:B10,G1)

In this SUMIFS formula we're doing "straight comparisons", no array "manipulation".

 

[xenou]

^^ Good summary of the pros and cons. I have heard that SumIfs will perform a little better than its alternative forms that we used in 2003 (where both are possible, anyway).

It's been about 5 years since I used the conditional sum wizard - does it actually input array formulas? Not to shabby then!

If you need to be 2003 compatible this page has some useful tips too:
http://www.contextures.com/xlFunctions01.html

By the way, I just joined the SUMIFS() club myself yesterday - I used it for the first time on a spreadsheet at work. Seemed pretty neat to me! Butmaybe it should be avoided if you are sharing files with unknown parties who may not have Excel 2007/2010.

 

[mark5767]

Thanks Biff!

Your comments are very helpful. I can see the additional power that array formulas can bring to bear on tables, especially when the records are not sorted very well for other functions.

This is quite nice because not having to spend time arranging and/or manipulating a table a certain way is a huge advantage. It appears array formulas power through the data and get to the correct result.

One thing since I'm new to array formulas is it appears every time you edit them you must use CTRL+SHIFT+ENTER to get the array braces to appear and have the formula work properly. It looks like this has been addressed elsewhere, but I just wanted to mention it again in this post for anyone new to array formulas like me.

So, for my purposes I think it's definitely worth getting more familiar with array formulas whether using the Conditional Sum Wizard or just writing them from scratch.

In your example I could not find a slick way to add the MONTH function to the array formula using the Wizard so I added that after the fact. I guess my last question I will throw out there is how helpful do people find the Conditional Sum Wizard? I find myself NOT ever using the MS Query Wizard, it just gets in the way. Maybe the same will hold true with the Conditional Sum Wizard once I get up to speed with writing array formulas.

Anyway, I digress. Thanks again for some great insight on this question!

 

[T. Valko]

Thanks Biff!

Your comments are very helpful. I can see the additional power that array formulas can bring to bear on tables, especially when the records are not sorted very well for other functions.

This is quite nice because not having to spend time arranging and/or manipulating a table a certain way is a huge advantage. It appears array formulas power through the data and get to the correct result.

One thing since I'm new to array formulas is it appears every time you edit them you must use CTRL+SHIFT+ENTER to get the array braces to appear and have the formula work properly. It looks like this has been addressed elsewhere, but I just wanted to mention it again in this post for anyone new to array formulas like me.

So, for my purposes I think it's definitely worth getting more familiar with array formulas whether using the Conditional Sum Wizard or just writing them from scratch.

In your example I could not find a slick way to add the MONTH function to the array formula using the Wizard so I added that after the fact. I guess my last question I will throw out there is how helpful do people find the Conditional Sum Wizard? I find myself NOT ever using the MS Query Wizard, it just gets in the way. Maybe the same will hold true with the Conditional Sum Wizard once I get up to speed with writing array formulas.

Anyway, I digress. Thanks again for some great insight on this question!

Array formulas are very powerful and versatile.

There are some things you may not be able to do without an array formula.

How helpful is the Conditional Sum Wizard? Well, I don't know! That's a matter of personal preference. I haven't used it in many years. I don't even have the add-in registered.

This may give you more insight on array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx