Countif using two columns?

  • Thread starter Thread starter Scott Stedman
  • Start date Start date
S

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

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

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

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


Regards
Claus Busch
 
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
Click to expand...

Great. It works now. Thanks Claus.
 
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.
 
Back
Top