Standard SUM example doesn't work?

[nurikabe]

The below seems to be a standard example for Excel 2000. I have seen it in a number of places.

--
To calculate the total value of cells F5:F25, where B5:B25 contains either "Northwind" or "Terra Firm", use the following formula.

=SUM(IF((B5:B25="Northwind")+(B5:B25="Terra Firm"),F5:F25))
--

Yet when I try this out I get a #VALUE! error.

Any ideas what's wrong?

 

[baggarwal]

I think if you click control + Shift + Enter after typing in the formula, the error message will go away. You should see some curly brackets appear at the end of the formula.

 

[nurikabe]

Thanks. I'm new to Excel land. What *is* that anyway? That's telling Excel to consider the range?

 

[baggarwal]

The curly brackets indicate an array formula.

 

[Dave Patton]

You may also want to consider SumProduct or Sumif which do not have to be array entered.

With criteria in B1 and C1
Each of the following gives the same result;


=SUMPRODUCT((B5:B26="Northwind")*1+(B5:B26="Terra Firm")*1,F5:F26)

=SUMPRODUCT((B5:B26=B1)*1+(B5:B26=C1)*1,F5:F26)

=SUMPRODUCT((B5:B26={"Northwind","Terra Firm"})*(F5:F26))

=SUMPRODUCT((B5:B26=B1:C1)*(F5:F26))

=SUMIF(B5:B25,B1,F5:F25)+SUMIF(B5:B25,C1,F5:F25)


or the array formula

{=SUM(IF((B5:B26="Northwind")+(B5:B26="Terra Firm"),F5:F26))}

Do not type the {} Excel presents these when formula is Array Entered with Ctrl+Shift+Enter (CSE).
This message was edited by Dave patton on 2002-10-13 12:35

 

[Yogi Anand]

Hi nurikabe, baggarwal, and Dave Patton:

a little variation on Dave Patton's assortment of SUMPRODUCT formulations
=SUMPRODUCT(((B5:B12="NorthWind")+(B5:B12="terraFirm"))*(F5:F12))


However Dave's array formula
{=SUM(IF((B5:B26="Northwind")+(B5:B26="Terra Firm"),F5:F26))}
did not work for me

instead I used the following array formula, and this one does seem to work
{=SUM(IF(B5:B26="NorthWind",F5:F26),IF(B5:B26="TerraFirm",F5:F26))}

Regards!

Yogi

Edit: Dave's SUM(IF ... formulation does indeed work. I had used the argument value "Terra Firm" in Dave's formulation incorrectly as "TerraFirm". If the argument values were correctly used, both (Dave's and mine) SUM(IF(... formulations will give correct results.
This message was edited by Yogi Anand on 2002-10-13 14:53

 

[Dave Patton]

On 2002-10-13 13:35, Yogi Anand wrote:
Hi nurikabe, baggarwal, and Dave Patton:

a little variation on Dave Patton's assortment of SUMPRODUCT formulations
=SUMPRODUCT(((B5:B12="NorthWind")+(B5:B12="terraFirm"))*(F5:F12))

However Dave's array formula
{=SUM(IF((B5:B26="Northwind")+(B5:B26="Terra Firm"),F5:F26))}
did not work for me

instead I used the following array formula, and this one does seem to work
{=SUM(IF(B5:B26="NorthWind",F5:F26),IF(B5:B26="TerraFirm",F5:F26))}

Regards!

Yogi

Yogi changed the name of the criteria; consequently, he gets different results.


The SumProduct formulas like
=SUMPRODUCT(((B5:B12="NorthWind")+(B5:B12="terraFirm"))*(F5:F12)) and the samples that I showed with all sections connected with "*" are not as robust as the others.

 

[Yogi Anand]

Hi Dave:

You are right! I did notice the mistake in my naming the criterion "Terra Firm" in your formulation to "TerraFirm" in mine.

Your SUM(IF( ... formulation does indeed work.

Regards!

Yogi

 

[Aladin Akyurek]

On 2002-10-13 13:35, Yogi Anand wrote:
Hi nurikabe, baggarwal, and Dave Patton:

a little variation on Dave Patton's assortment of SUMPRODUCT formulations
=SUMPRODUCT(((B5:B12="NorthWind")+(B5:B12="terraFirm"))*(F5:F12))

A syntactic variation, but, at times, with semantic consequences. By the way, when there is any text or formula returned blank in the range to sum, it is better to use , (comma) instead of * (star).

However Dave's array formula
{=SUM(IF((B5:B26="Northwind")+(B5:B26="Terra Firm"),F5:F26))}
did not work for me

It should work and it does for me. By the way, this is the formula Nurikabe was inquiring about...

instead I used the following array formula, and this one does seem to work
{=SUM(IF(B5:B26="NorthWind",F5:F26),IF(B5:B26="TerraFirm",F5:F26))}

I see one condition is wrong... Anyway one If too many...and requires repeating the range to sum.


Adding to Dave's list...

{=SUM(((B5:B25="Northwind")+(B5:B25="Terra Firm"))*F5:F25)}
This message was edited by Aladin Akyurek on 2002-10-13 14:59