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$3 0000))
but get no answer. Help.

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

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

Check out this link for more info...

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

Jim Thomlinson


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

Thanks Jim. It works perfectly.

"Jim Thomlinson" wrote:

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

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

Check out this link for more info...

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

Jim Thomlinson


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

Try this:

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

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

XL2002, WinXP


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

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

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


"Jim Thomlinson" wrote:

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

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

Check out this link for more info...

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

Jim Thomlinson


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

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

"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

how, they are different columns.

--

HTH

Bob Phillips

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

"rml" wrote in message
...
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...

"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

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

"Bob Phillips" wrote:

how, they are different columns.

--

HTH

Bob Phillips

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

"rml" wrote in message
...
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...

"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.

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.


"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"ronnomad" wrote:

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$3 0000))
but get no answer. Help.