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
|
|||
|
|||
Two Condition - search
I am trying to pull value when two conditions are met.
want to count column "B" when it = "ETARC" and when column "J" is = "0" or a negative number. I have tried =COUNTIF(B11:B26,"=ETARC")+COUNTIF(J8:J24,""0) the formula is only pulling the column "B" infor and the same number for column "J". My answer should be 6 for column "B" and 2 for column "J", instead I am getting the same 6 for the entire data reading only column "B" |
#2
|
|||
|
|||
Two Condition - search
xl2007 has a new =countifs() function that you can read about in Excel's help.
In any version of excel, you could use a formula like: =sumproduct(--(b11:b26="etarc"),--(j11:j26=0)) (I made the rows for both ranges the same.) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html linda wrote: I am trying to pull value when two conditions are met. want to count column "B" when it = "ETARC" and when column "J" is = "0" or a negative number. I have tried =COUNTIF(B11:B26,"=ETARC")+COUNTIF(J8:J24,""0) the formula is only pulling the column "B" infor and the same number for column "J". My answer should be 6 for column "B" and 2 for column "J", instead I am getting the same 6 for the entire data reading only column "B" -- Dave Peterson |
#3
|
|||
|
|||
Two Condition - search
linda wrote:
I am trying to pull value when two conditions are met. want to count column "B" when it = "ETARC" and when column "J" is = "0" or a negative number. I have tried =COUNTIF(B11:B26,"=ETARC")+COUNTIF(J8:J24,""0) the formula is only pulling the column "B" infor and the same number for column "J". My answer should be 6 for column "B" and 2 for column "J", instead I am getting the same 6 for the entire data reading only column "B" You can also look he http://www.contextures.com/xlFunctio...tml#SumProduct |
Thread Tools | |
Display Modes | |
|
|