Two Condition - search

[linda]

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]

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

 

[Glenn]

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