Countif using two columns?

[Scott Stedman]

I have a list of oil and gas wells by county and status. For example:

County Status
SEWARD SI
SEWARD Active
STEVENS Active
STEVENS SI
HASKELL Active
HASKELL SI
HASKELL Active

I want can easily use COUNITF to show that I have three SI (shut in)
wells and four active wells but I want to expand that data to show how
many SI and Active wells I have in each county. Is this possible?

 

[Claus Busch]

Hi Scott,

Am Fri, 6 May 2011 08:19:09 -0700 (PDT) schrieb Scott Stedman:

I have a list of oil and gas wells by county and status. For example:

County Status
SEWARD SI
SEWARD Active
STEVENS Active
STEVENS SI
HASKELL Active
HASKELL SI
HASKELL Active

I want can easily use COUNITF to show that I have three SI (shut in)
wells and four active wells but I want to expand that data to show how
many SI and Active wells I have in each county. Is this possible?

try it with a Pivot-Table
or use formula for e.g. SEWARD and SI:
=SUMPRODUCT(--(A1:A100="SEWARD"),--(B1:B100="SI"))


Regards
Claus Busch

 

[Scott Stedman]

Hi Scott,

Am Fri, 6 May 2011 08:19:09 -0700 (PDT) schrieb Scott Stedman:




try it with a Pivot-Table
or use formula for e.g. SEWARD and SI:
=SUMPRODUCT(--(A1:A100="SEWARD"),--(B1:B100="SI"))

Regards
Claus Busch

Thanks for reply. Forgive my ignorance - what is the purpose of the --
you have in the function. I am an "advanced novice" and have not used
SUMPRODUCT much.

 

[Claus Busch]

Hi Scott,

Am Fri, 6 May 2011 12:15:06 -0700 (PDT) schrieb Scott Stedman:

Thanks for reply. Forgive my ignorance - what is the purpose of the --
you have in the function. I am an "advanced novice" and have not used
SUMPRODUCT much.

the -- changes the boolean values TRUE and FALSE to 1 and 0


Regards
Claus Busch

 

[Scott Stedman]

Hi Scott,

Am Fri, 6 May 2011 12:15:06 -0700 (PDT) schrieb Scott Stedman:


the -- changes the boolean values TRUE and FALSE to 1 and 0

Regards
Claus Busch

Great. It works now. Thanks Claus.

 

[Claus Busch]

Hi Scott,

Am Fri, 6 May 2011 12:37:51 -0700 (PDT) schrieb Scott Stedman:

Great. It works now. Thanks Claus.

thank you for feeding back.
I would prefer to use PivotTable or in version 2007 or later COUNTIFS
Take a look:
http://www.claus-busch.de/Excel/Scott.zip


Regards
Claus Busch

 

[Dave Peterson]

You may want to consider using a pivottable, too.

And if you're using xl2007 or newer, there's a new =countifs() function that may
do what you want. See excel's help for more info.