in [Functions]

Prev: Count number of rows with specific text
Next: Partial Text Match then enter desired result into appropriate colu
From: linda on 24 May 2010 15:02 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"
From: Dave Peterson on 24 May 2010 15:07 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 here: 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
From: Glenn on 24 May 2010 15:13
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 here: http://www.contextures.com/xlFunctions04.html#SumProduct |