Count blanks cells

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

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

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