Count blanks cells

[jmumby]

Hi there,

Just wondering if anyone knows of a way to count blank cells on a sheet then place the result in the cell it was counted from. Assumeing it has a result itself.

In each cell I have have =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=1,"1"," "). This only gives me the result of '1' if the condition is true. Im looking to count how far between common results.

for example

+-----------+
| 1
+-----------+
|
+-----------+
|
+-----------+
|
+-----------+
|
+-----------+
| 4
+-----------+

I have had a look at COUNTBLANK but I don't think thats going to work for me.

Thanks!

Jason

[JLatham]

I'm not sure of how the rest of your sheet looks, but I'm going to assume
that at row 7 you have something like
A B C D E F G H I J K L
1 2 3 1 2 3 3 2 1 1 2 3

Then I will assume that you will type the value to find blanks between in
cell A16
Then in cell B16 put this formula in:
=IF(ISERROR(IF(MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)<0,"",MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0))),"",IF(MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)<0,"",MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)))

Extend that formula over to column K (one short of end of your data column)
and numbers will appear showing spaces between occurances of the number you
entered in cell A16. When numbers are next to one another, like 1 in columns
I and J, it will show zero (0). When matches aren't found , no entry will be
displayed.

The way it is written you can also extend it down the sheet and it will
always refer to row 7, but allow you to enter different numbers in column A
to examine several sets of spacings for different values.

I'm not certain this is exactly what you are looking for, but it's what I
envisioned you as looking for, at least to some degree. At least maybe it
will give you some more ideas. Check Excel Help for the MATCH() function to
see how it works.

The ISERROR() is in there to keep from displaying #NA errors when no match
at all is found, and the check for <0 is in there because in cases with
adjacent cells with the same value, you can end up with a negative number.


"jmumby" wrote:

>
> Hi there,
>
> Just wondering if anyone knows of a way to count blank cells on a sheet
> then place the result in the cell it was counted from. Assumeing it has
> a result itself.
>
> In each cell I have have =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=1,"1","
> "). Looking to count how far between common results.
>
> for example
>
> +-----------+
> | 1 |
> +-----------+
> | |
> +-----------+
> | |
> +-----------+
> | |
> +-----------+
> | |
> +-----------+
> | 4 |
> +-----------+
>
> I have had a look at COUNTBLANK but I don't think thats going to work
> for me.
>
> Thanks!
>
> Jason
>
>
> --
> jmumby
> ------------------------------------------------------------------------
> jmumby's Profile: http://www.excelforum.com/member.php...o&userid=34193
> View this thread: http://www.excelforum.com/showthread...hreadid=539582
>
>

[jmumby]

Thanks for the reply!

I might have confused things a bit!

My sheet looks like this.

+ A B C D E F G H I J K L M O P.....
1 1 2 3 4 3 3 2
2 8 4 2 5 6 3 5
3 9 3 4 5 2 7 5
4 8 6 7 1........

And repeats with random numbers down the spread sheet for about 900 rows. In column H or I the code I had done =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=1,"1"," it would put in a 1 if it occured in the row. In the next two rows it would be blank (no 1 in those rows). In the 4th row down it has a one but in row H I want it to put in '2' counting the two blank cells above.

It gets worse, in the next column I have =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=2,"1"," so would like this to do the same except obviously for 2.

I think this may be a bit out excels realm but it would be interesting to see if it could!

Thanks,

Jason

[Domenic]

Assumptions:

Columns A through G, starting with Row 2, contain the data

H1 and I1 contain the target numbers 1 and 2

The target number can occur more than once in any row

Defined Name:

Select H2

Insert > Name > Define

Name: Array

Refers to:

=(MMULT(--($A$2:$G2=H$1),TRANSPOSE(COLUMN($A$2:$G2)^0))>0)+0

Click Ok

Formula:

H2, copied down and across:

=IF(ISNUMBER(MATCH(H$1,$A2:$G2,0)),IF(SUM(Array)>1,ROWS(H$2:H2)-LARGE(IF(
Array,ROW($A$2:$G2)-ROW($A$2)+1),2)-1,1),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <jmumby.27foha_1146999002.4139@excelforum-nospam.com>,
jmumby <jmumby.27foha_1146999002.4139@excelforum-nospam.com> wrote:

> Thanks for the reply!
>
> I might have confused things a bit!
>
> My sheet looks like this.
>
> + A B C D E F G H I J K L M O P.....
> *1* 1 2 3 4 3 3 2
> *2* 8 4 2 5 6 3 5
> *3* 9 3 4 5 2 7 5
> *4* 8 6 7 1........
>
> And repeats with random numbers down the spread sheet for about 900
> rows. In column H or I the code I had done
> =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=1,"1"," it would put in a 1 if it
> occured in the row. In the next two rows it would be blank (no 1 in
> those rows). In the 4th row down it has a one but in row H I want it to
> put in '2' counting the two blank cells above.
>
> It gets worse, in the next column I have =IF(COUNTIF(A7:B7:C7:D7:E7:F7,
> "1")=2,"1"," so would like this to do the same except obviously for 2.
>
> I think this may be a bit out excels realm but it would be interesting
> to see if it could!
>
> Thanks,
>
> Jason

[jmumby]

Hey,

Thanks for your help!

I am pretty sure I followed your instructions word for word but I just seem to get blank cells now?

You can find the actual spreadsheet here http://labtrack.dpn.homeip.net/number_thing.zip
if you get an opportunity perhaps you can tell me where I am going wrong?

Thanks,

Jason

[Domenic]

It looks like for some reason you've entered the formula in an array of
cells. Instead, enter the formula in H2 only, confirm with
CONTROL+SHIFT+ENTER, and then copy/drag down and across. Also, since
Column G contains no data, adjust the ranges accordingly. Post back if
you need further help...

In article <jmumby.27i92q_1147119010.3468@excelforum-nospam.com>,
jmumby <jmumby.27i92q_1147119010.3468@excelforum-nospam.com> wrote:

> Hey,
>
> Thanks for your help!
>
> I am pretty sure I followed your instructions word for word but I just
> seem to get blank cells now?
>
> You can find the actual spreadsheet here
> http://labtrack.dpn.homeip.net/number_thing.zip
> if you get an opportunity perhaps you can tell me where I am going
> wrong?
>
> Thanks,
>
> Jason