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.

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

--
Biff
Microsoft Excel MVP


"RF" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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)

"RF" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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

hth,

Jim

"RF" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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" wrote:

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

Thanks to all, I found just what I needed.

On Tue, 28 Aug 2007 23:41:09 -0400, "Jim" <(E-Mail Removed)>
wrote:

>with your data in a1:a5 & b1:b5, in c1 enter:
>=sumproduct((a1:a5=1)*(b1:b5>0))
>
>hth,
>
>Jim
>
>"RF" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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.
>

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

--
HTH :>)
Dana DeLouis


"RF" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

Sun, 2 Sep 2007 20:23:26 -0400 from Dana DeLouis
<(E-Mail Removed)>:
> 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.


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

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

Stan Brown wrote:
>
> Sun, 2 Sep 2007 20:23:26 -0400 from Dana DeLouis
> <(E-Mail Removed)>:
> > 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.
>
>
> --
> "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/

--

Dave Peterson