# Standard SUM example doesn't work?

#### nurikabe

##### New Member
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### baggarwal

##### Well-known Member
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

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

#### baggarwal

##### Well-known Member
The curly brackets indicate an array formula.

#### Dave Patton

##### Well-known Member

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

##### MrExcel MVP
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

##### Well-known Member

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

##### MrExcel MVP
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

##### MrExcel MVP
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.

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

Replies
16
Views
243
Replies
2
Views
213
Replies
5
Views
484
Replies
2
Views
81
Replies
1
Views
323

### Forum statistics

1,143,641
Messages
5,719,988
Members
422,257
Latest member
Calion ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back