Counting Records in Excel?

  • Thread starter Thread starter Arsenio Oloroso
  • Start date Start date
A

Arsenio Oloroso

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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Elegant in its simplicity! Thank you, Anne.

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

=COUNTIF(F:F,"chicago")

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

www.OfficeArticles.com
www.MyExpertsOnline.com


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.
Click to expand...
Click to expand...
 
Back
Top