How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

=COUNTA(INDIRECT("A"&COUNTIF($2:$2,"3Morb_3*")&":E "&COUNTIF($2:$2,"3Morb_3*"
)))

--

HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

Thanks for that. I knew it would be possible.

RBS

"Bob Phillips" wrote in message
...
=COUNTA(INDIRECT("A"&COUNTIF($2:$2,"3Morb_3*")&":E "&COUNTIF($2:$2,"3Morb_3*"
)))

--

HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

Actually, it is not quite what I am after as in this formula there still is
the hard-coded column E.
The column E should be produced by the COUNTIF.
It looks close though.

RBS

"Bob Phillips" wrote in message
...
=COUNTA(INDIRECT("A"&COUNTIF($2:$2,"3Morb_3*")&":E "&COUNTIF($2:$2,"3Morb_3*"
)))

--

HTH

Bob Phillips

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

"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

Thanks, that works.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried with
and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number for
the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

the formula you show boils down to something like

say COUNTIF($3:$3,"3Test_3*") results in a value of 10 then
offset(I4,0,0,1,10) becomes I4:R4
Assume CountA(I4:R4) returns 5

then you formula becomes

=if(5<4,"NA",offset(H4,0,5)-offset(h4,0,5-3))

or if(5<4,"NA",M4-J4)

is that correct

What cell contains the formula?

--
Regards,
Tom Ogilvy








"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value groups I need
to take the last Value
(the one most to the right) and take of the Value in the group to the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

This gives me the right answer when the formula is in H5, but for some
reason when I copy down I get #Value
errors or NA.

=IF(COUNTA(I5:OFFSET(I5,0,COUNTIF($2:$2,"3Test_3*" )))<
6,"NA",OFFSET(H5,0,COUNTA(I5:OFFSET(I5,0,COUNTIF($ 2:$2,"3Test_3*")))) -
OFFSET(H5,0,COUNTA(I5:OFFSET(I5,0,COUNTIF($3:$3,"3 Test_3*")))-3))

RBS

"Tom Ogilvy" wrote in message
...
the formula you show boils down to something like

say COUNTIF($3:$3,"3Test_3*") results in a value of 10 then
offset(I4,0,0,1,10) becomes I4:R4
Assume CountA(I4:R4) returns 5

then you formula becomes

=if(5<4,"NA",offset(H4,0,5)-offset(h4,0,5-3))

or if(5<4,"NA",M4-J4)

is that correct

What cell contains the formula?

--
Regards,
Tom Ogilvy








"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

Bart,

I may be simplifying this too much, but couldn't you get the last item in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

That simplifies it a bit too much as there can be other data that I am
not interested in
more to the right.
This one works, with the formula's in column H:

=IF(COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"44P*")))<
6,"NA",OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($ 2:$2,"44P*")))) -
OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"4 4P*")))-3))

Although I get quite a few #VALUE errors that I don't understand yet.
It is not caused by the data it is looking at as that is fine.

RBS


Bob Phillips wrote:
Bart,

I may be simplifying this too much, but couldn't you get the last item in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

The #VALUE error happens if there is data to look at more than 21
columns the right.
No idea why this would be a problem.

RBS


wrote:
That simplifies it a bit too much as there can be other data that I am
not interested in
more to the right.
This one works, with the formula's in column H:

=IF(COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"44P*")))<
6,"NA",OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($ 2:$2,"44P*")))) -
OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"4 4P*")))-3))

Although I get quite a few #VALUE errors that I don't understand yet.
It is not caused by the data it is looking at as that is fine.

RBS


Bob Phillips wrote:
Bart,

I may be simplifying this too much, but couldn't you get the last item in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS

I must admit to being confused by what you are trying to do, but this bit

OFFSET(I3,0,COUNTIF($2:$2,"44P*")

shouldn't you multiply the COUNTIF by 3 and subtract 1 since they are 3
column offsets, otherwise you only get some of the data not all.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
The #VALUE error happens if there is data to look at more than 21
columns the right.
No idea why this would be a problem.

RBS


wrote:
That simplifies it a bit too much as there can be other data that I am
not interested in
more to the right.
This one works, with the formula's in column H:

=IF(COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"44P*")))<
6,"NA",OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($ 2:$2,"44P*")))) -
OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"4 4P*")))-3))

Although I get quite a few #VALUE errors that I don't understand yet.
It is not caused by the data it is looking at as that is fine.

RBS


Bob Phillips wrote:
Bart,

I may be simplifying this too much, but couldn't you get the last item
in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value
groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to
the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular
references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<

4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have
tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column
number
for the second formula?

So, if the first formula produced 5 the second one would be
right.


RBS

Yes, you are absolutely right, it needs to be multiplied by 3 and then minus
1.

So, with the fields in row one and the formula in column H this is the right
formula:

=IF(COUNTA(I2:OFFSET(I2,0,COUNTIF($1:$1,"44P*")*3-1))<
6,"NA",OFFSET(H2,0,COUNTA(I2:OFFSET(I2,0,COUNTIF($ 1:$1,"44P*")*3-1))) -
OFFSET(H2,0,COUNTA(I2:OFFSET(I2,0,COUNTIF($1:$1,"4 4P*")*3-1))-3))

It works now on my home PC and that might be it.
Thanks for spotting this bug.

RBS

"Bob Phillips" wrote in message
...
I must admit to being confused by what you are trying to do, but this bit

OFFSET(I3,0,COUNTIF($2:$2,"44P*")

shouldn't you multiply the COUNTIF by 3 and subtract 1 since they are 3
column offsets, otherwise you only get some of the data not all.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
The #VALUE error happens if there is data to look at more than 21
columns the right.
No idea why this would be a problem.

RBS


wrote:
That simplifies it a bit too much as there can be other data that I am
not interested in
more to the right.
This one works, with the formula's in column H:

=IF(COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"44P*")))<
6,"NA",OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($ 2:$2,"44P*")))) -
OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"4 4P*")))-3))

Although I get quite a few #VALUE errors that I don't understand yet.
It is not caused by the data it is looking at as that is fine.

RBS


Bob Phillips wrote:
Bart,

I may be simplifying this too much, but couldn't you get the last
item
in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value
groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to
the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular
references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<

4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have
tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column
number
for the second formula?

So, if the first formula produced 5 the second one would be
right.


RBS