Prev: data not carrying over from access to excel
Next: Drag Mouse and Add-up
From: b1llt on 25 Mar 2010 18:07
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
From: Bob Phillips on 25 Mar 2010 18:33
Try

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

--

HTH

Bob

"b1llt" <b1llt(a)discussions.microsoft.com> wrote in message
news:7D876373-E300-4A9D-921A-5419E0E9FF07(a)microsoft.com...
> 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


From: Ziggy on 25 Mar 2010 18:50
Try this


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

From: AGV on 25 Mar 2010 19:18
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$299>0),--($C$1:$C$299=$B300))
>
> --
>
> HTH
>
> Bob
>
> "b1llt" <b1llt(a)discussions.microsoft.com> wrote in message
> news:7D876373-E300-4A9D-921A-5419E0E9FF07(a)microsoft.com...
> > 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
>
>
> .
>
From: Ziggy on 26 Mar 2010 11:03
Another 2003 solution is an array formula

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

Set with CTRL-Shift-Enter
 |  Next  |  Last
Pages: 1 2
Prev: data not carrying over from access to excel
Next: Drag Mouse and Add-up