Countif with 2 conditions help

[CharlesW]

Hi,


I'm trying to write a formula with 2 conditions

A B

1 $161.00 60

2 $20.00 80

3 -$20.00 90

4 -$100.00 30


What I want to determine is :


Number of A that are >0 and > than .5 of B in the same row

Number of A that are >0 and < than .5 of B in the same row

Number of A that are <0 and > than .5 of B in the same row

Number of A that are <0 and < than .5 of B in the same row


and count each category.


I was trying to use this formula


=COUNTIF(A1:A1,">=0")-COUNTIF(A1:A1,">B1*.5")


but it's not taking in to account the second formula(syntax problem?) and additionally that only counts the 1 row and not all of the rows.


Any help would be greatly appreciated.

 

[Luke M]

Should be something like this. Note that your stated conditions were exclusive of the boundary condition, so I changed all of the ">" to become ">=". Change back if desired.

=SUMPRODUCT((A2:A100>=0)*(A2:A100>=0.5*B2:B100))

=SUMPRODUCT((A2:A100>=0)*(A2:A100<0.5*B2:B100))

=SUMPRODUCT((A2:A100<0)*(A2:A100>=0.5*B2:B100))

=SUMPRODUCT((A2:A100<0)*(A2:A100<0.5*B2:B100))

 

[CharlesW]

Thanks Luke M, that works perfectly.

 

[Luke M]

Terrific, glad I could help.

 

[CharlesW]

Oops, there is a problem. I'm only trying to count column A by rows. I'm not familiar with sumproduct but heres an example from my spread sheet.

A B

1 50 20

2 -150 30


=SUMPRODUCT((A2:A100>=0)*(A2:A100>=0.5*B2:B100)) returns 3 should be 1 (from row 1)

=SUMPRODUCT((A2:A100>=0)*(A2:A100<0.5*B2:B100)) returns 0 should be 0

=SUMPRODUCT((A2:A100<0)*(A2:A100>=0.5*B2:B100)) returns 0 should be 0

=SUMPRODUCT((A2:A100<0)*(A2:A100<0.5*B2:B100)) returns 1 should be 1 (from row 2)


This seems to be counting both conditions separately because the total returns 4 instead of 2. I'm not sure if I explained just counting the rows and not each value correctly.


Thanks.

 

[Hui]

Charles


I assume your data listed above as:

A B

1 50 20

2 -150 30


should be

A B

2 50 20

3 -150 30


Then the first formula

=SUMPRODUCT((A2:A100>=0)*(A2:A100>=0.5*B2:B100))


should be

=SUMPRODUCT((A2:A100>0)*(A2:A100>=0.5*B2:B100))