summing texts

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((D6:D67="Kentucky")*(1))+SUM((D6:D67="Washington")*(1))}

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

it becomes

=SUM((D6:D67="Kentucky")*(1))+SUM((D6:D67="Washington")*(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(D6:D67,"Kentucky")

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

=COUNTIF(D6:D67,"Kentucky")+COUNTIF(D6:D67,"Washington")

=SUMPRODUCT((D6:D67="Kentucky")+(D6:D67="Washington"))

=SUMPRODUCT(--(ISNUMBER(MATCH(D6:D67,{"Kentucky","Washington"},0))))

Better to use cells to hold the criteria:

A1 = Kentucky
A2 = Washington

Then:

=COUNTIF(D6:D67,A1)
=COUNTIF(D6:D67,A1)+COUNTIF(D6:D67,A2)
=SUMPRODUCT((D6:D67=A1)+(D6:D67=A2))
=SUMPRODUCT(--(ISNUMBER(MATCH(D6:D67,A1:A2,0))))

> {=SUM((D6:D67="Kentucky")*(1))+SUM((D6:D67="Washington")*(1))}
> 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

<thanh80@gmail.com> wrote in message
news:1142488647.186191.53170@i40g2000cwc.googlegroups.com...
>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((D6:D67="Kentucky")*(1))+SUM((D6:D67="Washington")*(1))}
>
> however when i try to modify the equation or click on ti
>
> it becomes
>
> =SUM((D6:D67="Kentucky")*(1))+SUM((D6:D67="Washington")*(1))
>
> and i get this error
>
> #VALUE!
>
> Where does the { } come from and how to correct the problem.
>