1. ## a formula, please? (2k up)

(brain in neutral and preferably without having to put an AND condition in a new column)
I want to count the number of instances in a range where the values in column A are 1 and the corresponding values in column B are not zero.
There are loads of cells where the values in B are zero but the values in A are not 1.

2. ## Re: a formula, please? (2k up)

Maybe I am misunderstanding what is wanted, but shouldn't the formula read =sumproduct((a1:a1000=1)*(b1:b1000>0)) in order " to count the number of instances in a range where the values in column A are 1 and the corresponding values in column B are not zero."

3. ## Re: a formula, please? (2k up)

Edited by HansV to correct formula

=SUMPRODUCT((A1:A1000=1)*(B1:B1000<>0))

4. ## Re: a formula, please? (2k up)

You're correct, I had switched = and <> by accident. Thanks!

5. ## Re: a formula, please? (2k up)

Thanks v much, folks.
<> 0 is better than >0 as values in column B can be negative.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•