Countif & "AND"

[Guest]

I have two columns of numbers but some cells in both columns are blank. I
only want to count the blank cells in one column if the corresponding cell in
the second column has a number. I have written the formula:
=COUNTIF($F$6:$F$3000,AND($F$6:$F$3000=0,$G$6:$G$3000>0))
but get no answer. Help.

 

[Guest]

Countif can only handle one criteria... You want to use sumproduct for that...

=sumproduct(--($F$6:$F$3000=0),--($G$6:$G$3000>0))

Check out this link for more info...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Thanks Jim. It works perfectly.

Countif can only handle one criteria... You want to use sumproduct for that...

=sumproduct(--($F$6:$F$3000=0),--($G$6:$G$3000>0))

Check out this link for more info...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Try this:

This will count true blanks with corresponding numbers
=SUMPRODUCT(ISBLANK(F6:F3000)*ISNUMBER(G6:G3000))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Interesting....Regarding:
=sumproduct(--($F$6:$F$3000=0),--($G$6:$G$3000>0))

That formula matches Col_F cells with zero in them (no surprise there), but
it also matches Col_G values containing only an apostrophe or text or TRUE or
FALSE.

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

this thread confuse a novice....someone has to check the real applicable
formula....when G:G contains a negative number...both formula confuse each
other...

 

[Bob Phillips]

how, they are different columns.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Bob...Im saying about different formula shown from the checked & unchecked !!
not about sub-formula for 2 columns .....hope u dig it.....without further
explaining...

 

[Guest]

hi ron...
I just read these guidelines......regarding this forum.....

Special consideration for question posts
In addition to the "helpful" and "not helpful" ratings, when someone adds a
post to a question you asked, you can rate this post as "the answer" to your
question or "not the answer" to your question. By rating a post as “the
answerâ€, you help others find the answer more quickly (because the Ask a
Question wizard searches answered questions), you give the person who posted
the answer credit for their contribution, and you help increase the quality
of answers in the discussion group. When you rate a post as the answer (click
Yes next to Did this post answer the question?) the post is marked as an
“answerâ€, the Questions answered number for the person who authored the post
increases by one in their profile information, and the thread is marked as an
“answered question.â€

When you rate a post as not the answer (click No next to Did this post
answer the question?), the post is not marked as an answer, the thread
remains a Question without answer thread (if there are no other posts marked
as an answer), and the author does not receive credit for answering the
question.

Notes

+MVPs can also mark a reply post in a question thread as an answer (or not).
+More than one post in a question thread can be marked as an answer to the
original question.