1. ## Count IF (03)

I have two columns, the first being a "name" and the second an "amount" I would like to count the cells in a range whose "name" is equal to "Hans" and whose "amount" is <> 0

For example:
=CountIf((D545="Hans")*(E5:E45,<>0))

I have tried array formulas and the like but can not resolve this one. It's been a long day....

John

2. ## Re: Count IF (03)

If you need more than one condition, you cannot use COUNTIF. Instead, use either

=SUMPRODUCT((D545="Hans")*(E5:E45<>0))

as a normal formula, or

=SUM((D545="Hans")*(E5:E45<>0))

as an array formula (confirm with Shift+Ctrl+Enter).

3. ## Re: Count IF (03)

G'Day Y'all,

I have a related question:
I need to count the number of times where in two different columns when any text is in column A and the corresponding cell in column B is blank. I need to ignore occurrences of column A blank and column B blank; and ignore occurrences of column A contains any text and column B any contains text .

Best regards,
Rich

4. ## Re: Count IF (03)

<P ID="edit" class=small>(Edited by sdckapr on 27-May-06 07:38. Added PS)</P>How about something like (change range as desired)
<pre>=SUMPRODUCT(ISTEXT(A1:A100)*ISBLANK(B1:B100)) </pre>

It counts (as requested)
Text in A and Blank A

It ignores (as requested)
Blank in A and Blank in B
Text in A and Text in B

It also ignores (not listed either way):
Blank in A and Text in B

Steve
PS: You can use, if you want Blank in A and Text in B to be counted:
=SUMPRODUCT(ISTEXT(A1:A100)*ISBLANK(B1:B100))+SUMP RODUCT(ISBLANK(A1:A100)*ISTEXT(B1:B100))

#### Posting Permissions

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