COUNTIF for 2 columns
 

I'm trying to set a parameter of the following in a formula in column J:
=COUNTIF($C:$C,$B300)
That also only counts the number of times in column J where a cells (J1:J299)
value is greater than "0". ----this is where I can't figure out the how
to??

In other words, I need to set-up a formula in column J row 300 that I want
to count
the number of times that column C is equal to cell B300 only if the value in
column J's cell is greater than zero.
Thanks, Bill

COUNTIF for 2 columns
 

Try

=SUMPRODUCT(--($J$1:$J$2990),--($C$1:$C$299=$B300))

--

HTH

Bob

"b1llt" wrote in message
...
I'm trying to set a parameter of the following in a formula in column J:
=COUNTIF($C:$C,$B300)
That also only counts the number of times in column J where a cells
(J1:J299)
value is greater than "0". ----this is where I can't figure out the how
to??

In other words, I need to set-up a formula in column J row 300 that I want
to count
the number of times that column C is equal to cell B300 only if the value
in
column J's cell is greater than zero.
Thanks, Bill

COUNTIF for 2 columns
 

Try this


=COUNTIFS(C1:C299,B300,J1:J299,"0")

COUNTIF for 2 columns
 

Great suggestion Bob, also if your using excel 2007 you can use the COUNTIFS
function:

=COUNTIFS($C$1:$C$299,$B$300,$J$1:$J$299,""&0)

AGV

"Bob Phillips" wrote:

Try

=SUMPRODUCT(--($J$1:$J$2990),--($C$1:$C$299=$B300))

--

HTH

Bob

"b1llt" wrote in message
...
I'm trying to set a parameter of the following in a formula in column J:
=COUNTIF($C:$C,$B300)
That also only counts the number of times in column J where a cells
(J1:J299)
value is greater than "0". ----this is where I can't figure out the how
to??

In other words, I need to set-up a formula in column J row 300 that I want
to count
the number of times that column C is equal to cell B300 only if the value
in
column J's cell is greater than zero.
Thanks, Bill


.

COUNTIF for 2 columns
 

Another 2003 solution is an array formula

=SUM(($K$15:$K$38=K41)*($M$15:$M$38=1))

Set with CTRL-Shift-Enter

COUNTIF for 2 columns
 

I should have mentioned we're still on Excel 2000.
I used these and am getting a #DIV/0! result.
Any suggestions ---thanks,
Bill

"Ziggy" wrote:

Another 2003 solution is an array formula

=SUM(($K$15:$K$38=K41)*($M$15:$M$38=1))

Set with CTRL-Shift-Enter
.

COUNTIF for 2 columns
 

There are no divide operations in that formula, so if you're seeing a
#DIV/0! result it's because you've got a #DIV/0! in the data being used by
the formula. Tackle the problem where it's being generated.
--
David Biddulph


"b1llt" wrote in message
...
I should have mentioned we're still on Excel 2000.
I used these and am getting a #DIV/0! result.
Any suggestions ---thanks,
Bill

"Ziggy" wrote:

Another 2003 solution is an array formula

=SUM(($K$15:$K$38=K41)*($M$15:$M$38=1))

Set with CTRL-Shift-Enter
.

COUNTIF for 2 columns
 

My bad! Your correct I did have it pulling a #DIV/O! into the data by mistake.
These all work great! Thanks everyone for all your help.
-B1llt

"David Biddulph" wrote:

There are no divide operations in that formula, so if you're seeing a
#DIV/0! result it's because you've got a #DIV/0! in the data being used by
the formula. Tackle the problem where it's being generated.
--
David Biddulph


"b1llt" wrote in message
...
I should have mentioned we're still on Excel 2000.
I used these and am getting a #DIV/0! result.
Any suggestions ---thanks,
Bill

"Ziggy" wrote:

Another 2003 solution is an array formula

=SUM(($K$15:$K$38=K41)*($M$15:$M$38=1))

Set with CTRL-Shift-Enter
.


.