CountIf Question

[RF]

Suppose I have two columns of numbers:

1 7
3 -6
1 0
4 3
1 4

In cell C1 I would like to count the number of instances that there is
a 1 in column A and a number greater than zero in column B.

The answer would be 2.

Thanks.

 

[T. Valko]

Try one of these:

If the values in column B will always only be numeric:

=SUMPRODUCT(--(A1:A5=1),--(B1:B5>0))

If column B might also contain TEXT values:

=SUMPRODUCT(--(A1:A5=1),--(ISNUMBER(B1:B5)),--(B1:B5>0))

 

[Pete]

Not Countif but how about -

=if(a1=1,if(b1>0,1,0),0) in cells C1 through C5
then in C6 =sum(c1:c5)

 

[Jim]

with your data in a1:a5 & b1:b5, in c1 enter:
=sumproduct((a1:a5=1)*(b1:b5>0))

hth,

Jim

 

[Guest]

Assuming that the values are in A1:A5 and B1:B5 then an array formula with
count function will do the trick.

=COUNT(IF((A1:A5=1)*(B1:B5>0),B1:B5))

Copy the formula into cell C1 and then select the cell.

To create the array formula:-

Press F2 and then CTRL+SHIFT+ENTER together.

It will place curly brackets around the formula. (You cannot put the curly
brackets in manually.)

If you edit the formula then you need to use the CTRL+SHIFT+ENTER when
finished editing.

Regards,

OssieMac

 

[RF]

Thanks to all, I found just what I needed.

with your data in a1:a5 & b1:b5, in c1 enter:
=sumproduct((a1:a5=1)*(b1:b5>0))

hth,

Jim

 

[Dana DeLouis]

With XL 2007...

=COUNTIFS(A1:A5,"=1",B1:B5,">0")

I don't like Excel 2007, but this is kind of neat...
Do a Alt+Enter to break up the function, and hit Ctrl+Shift+U to toggle an
expanded formula bar. Makes it nice to read ...imo.

=COUNTIFS(
A1:A5,"=1",
B1:B5,">0")

 

[Stan Brown]

Sun, 2 Sep 2007 20:23:26 -0400 from Dana DeLouis

I don't like Excel 2007, but this is kind of neat...
Do a Alt+Enter to break up the function, and hit Ctrl+Shift+U to toggle an
expanded formula bar. Makes it nice to read ...imo.

Is Ctrl-Shift-U necessary? In Excel 2003 it's not -- the formula bar
expands automatically for multiple lines with Alt-Enter. And with a
quick test on one formula, inserting Alt-Enter doesn't seem to hurt
the formula.

 

[Dave Peterson]

And alt-enters don't hurt the formulas in xl2003 and below, either.

(Although the formula bar won't expand and =countifs() is not available.)