COUNTIF?

  • Thread starter Thread starter Chuck Davis
  • Start date Start date
C

Chuck Davis

Using Excel 2003 on Window XP Pro. I have a workbood
containg two sheets of data and one that will be a
summary page.

The worksheet that contains the data looks like this:

Last Name First Village CAPT COMM

Alloway Herbert Golf Mesa

Anderson Ellie Golf Mesa
X
Atkinson Don Ridgecrest

Attebery Carol Canyon Crest X

Baker Richard Valley View

Baumann Bill Mountainview

Berg Elaine Tall Mesa

Blum Herbert Golf Mesa

Boschetto Pat Tall Mesa X

Bosco Janice Highlands
X
Bruner Mary Golf Mesa X

Buckelew Jesse Big Sky

Buckmaster Robert Tall Mesa X
X
Campbell Lisa Big Sky
X
 
Sorry about that goof, this is the message that I wanted
to place.

Excel 2003, Win XP Pro
I have a workbook containing three sheets. Roster,
Addresses, Skills & Summary

This is a brief layout of the Skills worksheet.

Name Village BLK CAPT COMM
COORD COUN DOC
Adams Golf Mesa X

Bright Mountain View X

Blum Big Sky
X
Cicero Golf Mesa
X
Davii Valley View X

Brill Highlands
X
Land Big Sky
X

On my summary page, I want to create this
Golf Mesa Mountain View Big Sky
Valley View Highlands
BLK CAPT 1
COMM
COORD 1
1
COUN 1
DOC 2
1


This is my attempt to place the count of the number DOC's
in Big Sky.
=COUNTIF(Skills!c2:c140,"Big Sky",Skills!e2:e140,"X")

In the error message the e140 is highlighted.
 
XL does not have "docs", although Word saves in that format. If that iws not
what you intend to say, try:
=COUNTIF(rng,"DOCS")
 
DOC is a word in an Excel cell. It has nothing to do with
the file extension for a Microsoft Word file. It's
unfortunate that one is unable to post an image of the
actual worksheet.
 
=COUNTIF(Skills!c2:c140,"Big Sky",Skills!e2:e140,"X")

The COUNTIF syntax above is incorrect, that's why you've got an error

This kind of syntax will work

: =COUNTIF(Skills!c2:c140,"Big Sky") + COUNTIF(Skills!e2:e140,"X")

But if I read your intent correctly ..
what you're looking for can be via use of SUMPRODUCT:

Try : =SUMPRODUCT((Skills!C3:C15="Big Sky")*(Skills!E3:E15="X"))

The above will return the count of the # of "X"s in col E for "Big Sky" in
col C

(Note that the ranges of cols C and E must be identical,
and you can't use complete cols, for e.g. "C:C" , "E:E" in the formula)

--
hth
Max[/QUOTE]
 
Max, Thank you, thank you, thank you!
It formula worked, but only after doing a global change
to change the "X" to an numeral "1" and then back to
an "X". I don't know the attributes of those columns that
were infecting the calculations. I inherited the document
from another volunteer that had given up. She was trying
to create a PivotTable for the purpose. I couldn't get
that to work either.

Chuck
 
Back
Top