Standard SUM example doesn't work?

nurikabe

New Member
Joined
Sep 22, 2002
Messages
10
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
Joined
Jul 10, 2002
Messages
591
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
Joined
Sep 22, 2002
Messages
10
Thanks. I'm new to Excel land. What *is* that anyway? That's telling Excel to consider the range?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,960
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Feb 15, 2002
Messages
4,960
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

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

Which adblocker are you using?

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
Top