Counting Records in Excel?

Basically...
I'm trying to figure out how to do something in Excel that could readily
be done in MS Access.

I have an Excel list, and I want to know how many records contain
"Chicago" in one of the fields.

I thought up this array formula:

=SUM(IF(F2:F1123="chicago",1,0))

The idea is that, for every cell containing "chicago," Excel would enter
a "1" in a duplicate range and a "0" if the cell didn't contain
"chicago." Excel then would add up the ones and tell me how many
records in the list were from...Chicago.

This doesn't appear to work after I do [control][enter]. I get a "0."
What am I doing wrong? Is my syntax incorrect? Am I even on the right
track?

Any leads are much appreciated.

You're making it too difficult. Just use this:

=COUNTIF(F:F,"chicago")

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Arsenio Oloroso" <[email protected]> wrote in message
news:[email protected]...
> Basically...
> I'm trying to figure out how to do something in Excel that could readily
> be done in MS Access.
>
> I have an Excel list, and I want to know how many records contain
> "Chicago" in one of the fields.
>
> I thought up this array formula:
>
> =SUM(IF(F2:F1123="chicago",1,0))
>
> The idea is that, for every cell containing "chicago," Excel would enter
> a "1" in a duplicate range and a "0" if the cell didn't contain
> "chicago." Excel then would add up the ones and tell me how many
> records in the list were from...Chicago.
>
> This doesn't appear to work after I do [control][enter]. I get a "0."
> What am I doing wrong? Is my syntax incorrect? Am I even on the right
> track?
>
> Any leads are much appreciated.
>

Arsenio,

Anne's solution is the way to go. BUt I'd thought I'd mention this. You
were pretty close. Your formula would not cycle through F2:F1123 unless you
made it an array formula, simply by using Ctrl-Shift-Enter to commit it to
the cell, instead of just enter.

If you'll be needing a total for every entry in that column (Chicago,
Springfield, Hoboken, Bumfolk, etc.), consider a pivot table. They're easy,
and give you a total (sum, count, etc.) for every kind of entry found.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Arsenio Oloroso" <[email protected]> wrote in message
news:[email protected]...
> Basically...
> I'm trying to figure out how to do something in Excel that could readily
> be done in MS Access.
>
> I have an Excel list, and I want to know how many records contain
> "Chicago" in one of the fields.
>
> I thought up this array formula:
>
> =SUM(IF(F2:F1123="chicago",1,0))
>
> The idea is that, for every cell containing "chicago," Excel would enter a
> "1" in a duplicate range and a "0" if the cell didn't contain "chicago."
> Excel then would add up the ones and tell me how many records in the list
> were from...Chicago.
>
> This doesn't appear to work after I do [control][enter]. I get a "0."
> What am I doing wrong? Is my syntax incorrect? Am I even on the right
> track?
>
> Any leads are much appreciated.
>

And.......if you want to "see" the "chicago" data as well, the AutoFilter
feature works wonders to help identify type-o's etc., along with the
SUBTOTAL function for counting ,averaging, suming, etc.

Vaya con Dios,
Chuck, CABGx3




"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> Arsenio,
>
> Anne's solution is the way to go. BUt I'd thought I'd mention this. You
> were pretty close. Your formula would not cycle through F2:F1123 unless
you
> made it an array formula, simply by using Ctrl-Shift-Enter to commit it to
> the cell, instead of just enter.
>
> If you'll be needing a total for every entry in that column (Chicago,
> Springfield, Hoboken, Bumfolk, etc.), consider a pivot table. They're
easy,
> and give you a total (sum, count, etc.) for every kind of entry found.
>
> --
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Arsenio Oloroso" <[email protected]> wrote in message
> news:[email protected]...
> > Basically...
> > I'm trying to figure out how to do something in Excel that could readily
> > be done in MS Access.
> >
> > I have an Excel list, and I want to know how many records contain
> > "Chicago" in one of the fields.
> >
> > I thought up this array formula:
> >
> > =SUM(IF(F2:F1123="chicago",1,0))
> >
> > The idea is that, for every cell containing "chicago," Excel would enter
a
> > "1" in a duplicate range and a "0" if the cell didn't contain "chicago."
> > Excel then would add up the ones and tell me how many records in the
list
> > were from...Chicago.
> >
> > This doesn't appear to work after I do [control][enter]. I get a "0."
> > What am I doing wrong? Is my syntax incorrect? Am I even on the right
> > track?
> >
> > Any leads are much appreciated.
> >
>
>

Elegant in its simplicity! Thank you, Anne.

Anne Troy wrote:
> You're making it too difficult. Just use this:
>
> =COUNTIF(F:F,"chicago")
>
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "Arsenio Oloroso" <[email protected]> wrote in message
> news:[email protected]...
>
>>Basically...
>>I'm trying to figure out how to do something in Excel that could readily
>>be done in MS Access.
>>
>>I have an Excel list, and I want to know how many records contain
>>"Chicago" in one of the fields.
>>
>>I thought up this array formula:
>>
>>=SUM(IF(F2:F1123="chicago",1,0))
>>
>>The idea is that, for every cell containing "chicago," Excel would enter
>>a "1" in a duplicate range and a "0" if the cell didn't contain
>>"chicago." Excel then would add up the ones and tell me how many
>>records in the list were from...Chicago.
>>
>>This doesn't appear to work after I do [control][enter]. I get a "0."
>>What am I doing wrong? Is my syntax incorrect? Am I even on the right
>>track?
>>
>>Any leads are much appreciated.
>>
>
>
>

Thanks Earl,
I forgot to press the shift key along with control and enter.


Earl Kiosterud wrote:

> Arsenio,
>
> Anne's solution is the way to go. BUt I'd thought I'd mention this. You
> were pretty close. Your formula would not cycle through F2:F1123 unless you
> made it an array formula, simply by using Ctrl-Shift-Enter to commit it to
> the cell, instead of just enter.
>
> If you'll be needing a total for every entry in that column (Chicago,
> Springfield, Hoboken, Bumfolk, etc.), consider a pivot table. They're easy,
> and give you a total (sum, count, etc.) for every kind of entry found.
>