Counting Records in Excel?

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.
 
E

Earl Kiosterud

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.
 
C

CLR

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...
 
A

Arsenio Oloroso

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...
 
Top