| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Nested array functions? | Excel Worksheet Functions | |||
| IF statement in Array Formula's | Excel Discussion (Misc queries) | |||
| Nested if in Array | Excel Worksheet Functions | |||
| How do I use wild cards in nested array formulas? | Excel Worksheet Functions | |||
Linear Mode
