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

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

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