CountIf Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RF - 29 Aug 2007 03:59 GMT

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.

Reply to this Message

T. Valko - 29 Aug 2007 04:20 GMT

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))

Signature

Biff
Microsoft Excel MVP

> Suppose I have two columns of numbers:
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

Pete - 29 Aug 2007 04:30 GMT

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)

> Suppose I have two columns of numbers:
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

Jim - 29 Aug 2007 04:41 GMT

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

hth,

Jim

> Suppose I have two columns of numbers:
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

RF - 29 Aug 2007 13:16 GMT

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))
[quoted text clipped - 17 lines]
>>
>> Thanks.

Reply to this Message

OssieMac - 29 Aug 2007 04:50 GMT

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

> Suppose I have two columns of numbers:
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

Dana DeLouis - 03 Sep 2007 01:23 GMT

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")

Signature

HTH :>)
Dana DeLouis

> Suppose I have two columns of numbers:
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

Stan Brown - 03 Sep 2007 13:04 GMT

Sun, 2 Sep 2007 20:23:26 -0400 from Dana DeLouis
<ddelouis@bellsouth.net>:

> 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.

Signature

"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

Dave Peterson - 03 Sep 2007 13:09 GMT

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.)

> Sun, 2 Sep 2007 20:23:26 -0400 from Dana DeLouis
> <ddelouis@bellsouth.net>:
[quoted text clipped - 12 lines]
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/

Signature

Dave Peterson

Reply to this Message