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

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

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