MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Two Condition - search

• Follow

```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"
```
 0

```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
```
 0

```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
```
 0

2 Replies
202 Views

Similiar Articles:

7/25/2012 12:15:47 PM