COUNTBLANK function

[Mike]

Happy New Year Everyone,

Using Excel XP.

I have 3 columns of data, A1:C10. I want to count the blanks in C1:C10 ONLY
if there is data in A1:A10.

Example:

A B C
----------------------------------------------
1 20 6 5
2 4 1
3 8 6 2
4 4 2
5 8 1 1
6 7 2
7 3 2 2
8
9
10

In the above example I need a formula that counts the blanks in C1:C10 but
only if there is data in A1:A10, so that I get a total of
3 (C2,C4 & C6 are blank). When I use the countblank function it counts
all the blanks in C1:C10 (6).

Thanks in advance,
Mike

 

[Dave Peterson]

=sumproduct(--(a1:a10<>""),--(c1:c10=""))

=sumproduct() likes to work with numbers.

The -- converts True's and False's to 1's and 0's.

 

[Ron Rosenfeld]

Happy New Year Everyone,

Using Excel XP.

I have 3 columns of data, A1:C10. I want to count the blanks in C1:C10 ONLY
if there is data in A1:A10.

Example:

A B C
----------------------------------------------
1 20 6 5
2 4 1
3 8 6 2
4 4 2
5 8 1 1
6 7 2
7 3 2 2
8
9
10

In the above example I need a formula that counts the blanks in C1:C10 but
only if there is data in A1:A10, so that I get a total of
3 (C2,C4 & C6 are blank). When I use the countblank function it counts
all the blanks in C1:C10 (6).

Thanks in advance,
Mike

Something like:

=SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))

One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.

If the data in column A is the result of a formula, you may need to test for
whatever it is that the formula is returning to look like an empty cell; or you
could test for a number being present in the cell, if number is the only valid
entry.


--ron

 

[Bob Phillips]

Mike,

This is one way

=SUMPRODUCT(--(A1:A10<>""),--(C1:C10=""))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

Got it working!! Thanks for the quick response,

Mike

 

[no_name]

Something like:

=SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))

One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.

If the data in column A is the result of a formula, you may need to test for
whatever it is that the formula is returning to look like an empty cell; or you
could test for a number being present in the cell, if number is the only valid
entry.


--ron

That may answer a question I was going to ask. I have a spreadsheed I'm
trying to use for daily and weekly averages.

Column C is a morning value, column D is an evening value and column E
is the daily average, column F has a weekly average.

Because of the data collection, there may be one or many values missing
during a week. I need to average what is there. In the meantime, until
there's data, I want the cells where I am averaging to remain blank.

I'm trying to make it look neat by having it maintain blank cells in
columns E & F until data is input into one of the cells in column C or D.

This formula works in Cell E9:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(D9))),AVERAGE(C99),"")

It also works in E10, E11 ... E375 with the appropriate values for the row.

I know it's a kludge, but it does give me a blank cell until there's
data in cells C9 or D9.

For the weekly average, I tried the following in Cell F15:

=IF(OR(NOT(ISBLANK(E9)),NOT(ISBLANK(E10))),AVERAGE(E9:E15),"")

It gives me a #DIV/0 error until there's some data in cell C9
(or in D9, C10 or D10).

But if I directly test cell C9 or C10 it does work:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(C10))),AVERAGE(E9:E15),"")

However, what I need is a generic test for the whole week's data, that
there's a value somewhere in C915

I'll give the SUMPRODUCT function a try, but I'm open to suggestions.

 

[Ron Rosenfeld]

That may answer a question I was going to ask. I have a spreadsheed I'm
trying to use for daily and weekly averages.

Column C is a morning value, column D is an evening value and column E
is the daily average, column F has a weekly average.

Because of the data collection, there may be one or many values missing
during a week. I need to average what is there. In the meantime, until
there's data, I want the cells where I am averaging to remain blank.

I'm trying to make it look neat by having it maintain blank cells in
columns E & F until data is input into one of the cells in column C or D.

This formula works in Cell E9:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(D9))),AVERAGE(C99),"")

This might be a little simpler:

=IF(COUNT(C99)=0,"",AVERAGE(C99))

It also works in E10, E11 ... E375 with the appropriate values for the row.

I know it's a kludge, but it does give me a blank cell until there's
data in cells C9 or D9.

For the weekly average, I tried the following in Cell F15:

=IF(OR(NOT(ISBLANK(E9)),NOT(ISBLANK(E10))),AVERAGE(E9:E15),"")

It gives me a #DIV/0 error until there's some data in cell C9
(or in D9, C10 or D10).

But if I directly test cell C9 or C10 it does work:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(C10))),AVERAGE(E9:E15),"")

However, what I need is a generic test for the whole week's data, that
there's a value somewhere in C915

Actually, all you have to test for is E9:E15; and you can do it the same as
above:

=IF(COUNT(E9:E15)=0,"",AVERAGE(E9:E15))

I'll give the SUMPRODUCT function a try, but I'm open to suggestions.

--ron

 

[no_name]

Actually, all you have to test for is E9:E15; and you can do it the same as
above:

=IF(COUNT(E9:E15)=0,"",AVERAGE(E9:E15))

Thanks.

I had done a similar sheet before and I knew the solution was a lot
simpler than all that nested IF(NOT(OR))) kludge.

I just couldn't remember it or figure it out again.