nested array formula's

[Leoski]

Microsoft has an example of how to count how often multiple text values occur
by using the SUM and IF fuctions. The example formula is:
{=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0))}
Copying and pasting the sample table and formulas work fine - since array
formulas I pressed F2 then Shift+Ctrl+Enter. Formula works. But if I type
in the exact formula (not copy & paste), it won't work. The F2 key does not
highlight it. The Evaluate Formula tool says my formula contains a constant
- but it is identical to the copied one that works. What am I doing wrong?
Thank you

 

[T. Valko]

When you manually type in the formula are you typing the { } ?

Don't type the { }. Just type the formula:

=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0))

Then, instead of hitting the ENTER key use the key combination of
CTRL,SHIFT,ENTER. Excel will then add the braces { } automatically.

Another way to write that formula that does not require array entry (just
hit ENTER):

=COUNTIF(A2:A7,"Buchanan")+COUNTIF(A2:A7,"Dodsworth")

Biff

 

[Leoski]

Thanks - that answer saved what hair I have left!

 

[T. Valko]

You're welcome.

Biff

Thanks - that answer saved what hair I have left!

 

[Teethless mama]

=SUM(COUNTIF(A2:A7,{"Buchanan","Dodsworth"}))
or
=SUMPRODUCT(--(A2:A7={"Buchanan","Dodsworth"}))