# 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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

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

The curly brackets indicate an array formula.

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

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

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.

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

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

Replies
1
Views
426
Replies
1
Views
559
Replies
11
Views
916
Replies
2
Views
139
Replies
3
Views
405

Threads
1,219,999
Messages
6,151,381
Members
451,025
Latest member
Katinthehat

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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