software

I use to be able to do this but now seems like i forgot

I want sum up values based on words in a column. For example in a
column I want to aassign a value of 1 for every word in the column that
has Kentucky

This is the equation that used ot work for excel

{=SUM((D667=quot;Kentuckyquot;)*(1)) SUM((D667=quot;Washin gtonquot;)*(1))}

however when i try to modify the equation or click on ti

it becomes

=SUM((D667=quot;Kentuckyquot;)*(1)) SUM((D667=quot;Washing tonquot;)*(1))

and i get this error

#VALUE!

Where does the { } come from and how to correct the problem.Hi!

If you only want to count one criteria:

=COUNTIF(D667,quot;Kentuckyquot;)

If you want to count more than one criteria, try one of these:

=COUNTIF(D667,quot;Kentuckyquot;) COUNTIF(D667,quot;Washin gtonquot;)

=SUMPRODUCT((D667=quot;Kentuckyquot;) (D667=quot;Washingto nquot;))

=SUMPRODUCT(--(ISNUMBER(MATCH(D667,{quot;Kentuckyquot;,quot;Washingtonquot;},0 ))))

Better to use cells to hold the criteria:

A1 = Kentucky
A2 = Washington

Then:

=COUNTIF(D667,A1)
=COUNTIF(D667,A1) COUNTIF(D667,A2)
=SUMPRODUCT((D667=A1) (D667=A2))
=SUMPRODUCT(--(ISNUMBER(MATCH(D667,A1:A2,0))))

gt; {=SUM((D667=quot;Kentuckyquot;)*(1)) SUM((D667=quot;Washin gtonquot;)*(1))}
gt; Where does the { } come from and how to correct the problem.

The braces mean that the formula is an array formula. Excel places them
around the formula when you enter the formula. Instead of just hitting the
ENTER key like you normally would, you need to use a sequence of key
strokes. That key sequence is CTRL,SHIFT,ENTER. Hold down both the CTRL key
and the SHIFT key then hit ENTER. You can't just type these braces in, you
MUST use the key sequence. Also, when you edit an array formula it must be
re-entered as an array using the key sequence.

For the task that you're doing you don't need an array formula. Use one of
the examples I've posted above.

Biff

gt; wrote in message ups.com...
gt;I use to be able to do this but now seems like i forgot
gt;
gt; I want sum up values based on words in a column. For example in a
gt; column I want to aassign a value of 1 for every word in the column that
gt; has Kentucky
gt;
gt; This is the equation that used ot work for excel
gt;
gt; {=SUM((D667=quot;Kentuckyquot;)*(1)) SUM((D667=quot;Washin gtonquot;)*(1))}
gt;
gt; however when i try to modify the equation or click on ti
gt;
gt; it becomes
gt;
gt; =SUM((D667=quot;Kentuckyquot;)*(1)) SUM((D667=quot;Washing tonquot;)*(1))
gt;
gt; and i get this error
gt;
gt; #VALUE!
gt;
gt; Where does the { } come from and how to correct the problem.
gt;