Create a total based on multiple conditions is not giving correct.
 

following instructions copied from microsoft excel giving result as #value
when applied. Please help me.
Create a total based on multiple conditions
Use the following formula to calculate the total value of cells F5:F25,
where B5:B25 contains "Northwind" and the range C5:C25 contains the region
name "Western".

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25))

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))

Both of these formulas are array formulas and must be entered by pressing
CTRL+SHIFT+ENTER. Learn about array formulas.

Works for me, are you sure you entered it correctly with
ctrl + shift & enter? I prefer this more elegant solution

=SUMPRODUCT((B5:B25={"Northwind","Terra Firm"})*(F5:F25))

entered normally

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Jacob" wrote in message
...
following instructions copied from microsoft excel giving result as #value
when applied. Please help me.
Create a total based on multiple conditions
Use the following formula to calculate the total value of cells F5:F25,
where B5:B25 contains "Northwind" and the range C5:C25 contains the region
name "Western".

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25))

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))

Both of these formulas are array formulas and must be entered by pressing
CTRL+SHIFT+ENTER. Learn about array formulas.

Hi!

The formulas are correct and will work but you must enter
them with the key combination of CTRL,SHIFT,ENTER.

Don't just hit ENTER. Hold down the CTRL key and the SHIFT
key and then hit ENTER.

You can rewrite that formula as:(also an array)

=SUM((B5:B15="NORTHWIND")*(C5:C15="WESTERN")*(F5:F 15))

Or, even better:(not an array)

=SUMPRODUCT(--(B5:B15="NORTHWIND"),--
(C5:C15="WESTERN"),F5:F15)

Biff

-----Original Message-----
following instructions copied from microsoft excel giving
result as #value
when applied. Please help me.
Create a total based on multiple conditions
Use the following formula to calculate the total value of
cells F5:F25,
where B5:B25 contains "Northwind" and the range C5:C25
contains the region
name "Western".

=SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F 5:F25))

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))

Both of these formulas are array formulas and must be
entered by pressing
CTRL+SHIFT+ENTER. Learn about array formulas.


.