If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
COUNTIF for 2 columns
Try this
=COUNTIFS(C1:C299,B300,J1:J299,"0") |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 . |
#7
|
|||
|
|||
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 . |
#8
|
|||
|
|||
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 . . |
Thread Tools | |
Display Modes | |
|
|