Receiving #VALUE! when using examples from Microsoft Excel, why?
 

Using an example from "Count how often multiple text or number values occur
by using functions" returns #VALUE! Why? Currently using Excel 2002 with
Add-Ins Conditional Sum Wizard, Solver Add-in, and Lookup Wizard.
fx:
=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0)))

I have tried using other examples using COUNT and get same results.
I have two columns, one is a number the other text, that need to match two
logicals to return a sum or count of those that match.
Please help - Thanks, RickG

Receiving #VALUE! when using examples from Microsoft Excel, why?
 

1) This is an array function. When you type it in (or open it for editing
with double click or F2) you need to complete the process with
CTRL+SHIFT+ENTER not just ENTER

2) That is a very poor example when SUMPRODUCT is so much simpler to use.
SUMPRODUCT does not need CTRL+SHIFT+ENTER
=SUMPRODUCT(--(A2:A7="Buchanan),--(B2:B7<9000))
For more details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RickG" wrote in message
...
Using an example from "Count how often multiple text or number values
occur
by using functions" returns #VALUE! Why? Currently using Excel 2002 with
Add-Ins Conditional Sum Wizard, Solver Add-in, and Lookup Wizard.
fx:
=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0)))

I have tried using other examples using COUNT and get same results.
I have two columns, one is a number the other text, that need to match two
logicals to return a sum or count of those that match.
Please help - Thanks, RickG

Receiving #VALUE! when using examples from Microsoft Excel, wh
 

Thank you very much Bernard!
CTRL+SHIFT+ENTER works and SUMPRODUCT works great!

"Bernard Liengme" wrote:

1) This is an array function. When you type it in (or open it for editing
with double click or F2) you need to complete the process with
CTRL+SHIFT+ENTER not just ENTER

2) That is a very poor example when SUMPRODUCT is so much simpler to use.
SUMPRODUCT does not need CTRL+SHIFT+ENTER
=SUMPRODUCT(--(A2:A7="Buchanan),--(B2:B7<9000))
For more details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RickG" wrote in message
...
Using an example from "Count how often multiple text or number values
occur
by using functions" returns #VALUE! Why? Currently using Excel 2002 with
Add-Ins Conditional Sum Wizard, Solver Add-in, and Lookup Wizard.
fx:
=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0)))

I have tried using other examples using COUNT and get same results.
I have two columns, one is a number the other text, that need to match two
logicals to return a sum or count of those that match.
Please help - Thanks, RickG