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
 | 
Pages: 1
Prev: Count number of rows with specific text
Next: Partial Text Match then enter desired result into appropriate colu