error in formula

[Jeff]

Using Excel 2007 and working on a xls file I am trying to count the
occurrences of a certain value (in this case "1940 Census") in a column.
I tried to follow the example in the XL Help and used this formula:

=SUM(IF((L2:L303="1940 Census"),1,0))

but the result I get is
#VALUE!
and the error info says "a value used in the formula is the wrong type"

The column L2-L303 contains either blank cells, or "1940 Census", or
just words like "immigration", "History", etc.

Can someone suggest how I should correct this formula?

I also want the formula to ignore case

Thanks.

Jeff

 

[Gord Dibben]

You should be using COUNTIF

=COUNTIF(L2:L303,"1940 Census")


Gord Dibben MS Excel MVP.

 

[joeu2004]

Using Excel 2007 and working on a xls file I am trying
to count the occurrences of a certain value (in this case
"1940 Census") in a column. I tried to follow the example
in the XL Help and used this formula:
=SUM(IF((L2:L303="1940 Census"),1,0))
but the result I get is #VALUE!

Possibly because that needs to be entered as an array formula, and you
neglected to press ctrl+shift+Enter instead of just Enter.

No matter. That is a poor solution. A better solution is the
following __normal__ formula (just press Enter as usual):

=COUNTIF(L2:L303,"1940 census")

 

[Jeff]

You should be using COUNTIF

=COUNTIF(L2:L303,"1940 Census")


Gord Dibben MS Excel MVP.

Thank you very much.

 

[Jeff]

Possibly because that needs to be entered as an array formula, and you
neglected to press ctrl+shift+Enter instead of just Enter.

No matter. That is a poor solution. A better solution is the
following __normal__ formula (just press Enter as usual):

=COUNTIF(L2:L303,"1940 census")

Thank you.

I was just trying to follow the example given in XL's help.

 

[joeu2004]

=SUM(IF((L2:L303="1940 Census"),1,0))

[....]
No matter.  That is a poor solution.

[....]
I was just trying to follow the example given in XL's help.

Examples are constructed to demonstrate syntax and usage. Often they
are purposely over-simplified for that purpose. They are not
necessarily meant to demonstrate the best solution.