nested array formula's
 

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

nested array formula's
 

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,"Dodswort h")

Biff

"Leoski" wrote in message
...
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

nested array formula's
 

Thanks - that answer saved what hair I have left!

"T. Valko" wrote:

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,"Dodswort h")

Biff

"Leoski" wrote in message
...
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

nested array formula's
 

You're welcome.

Biff

"Leoski" wrote in message
...
Thanks - that answer saved what hair I have left!

"T. Valko" wrote:

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,"Dodswort h")

Biff

"Leoski" wrote in message
...
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

nested array formula's
 

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


"Leoski" wrote:

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