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" <(E-Mail Removed)> wrote in message
news:#GYwu2$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:#GYwu2$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:#GYwu2$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:%23GYwu2$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%23GYwu2$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%23GYwu2$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> =CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>> news:%23GYwu2$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> =CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>> news:%23GYwu2$(E-Mail Removed)...
>>> 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,"3Test_3*")))-3))

RBS

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> =CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))
>>>
>>>
>>> --
>>> Regards,
>>> Tom Ogilvy
>>>
>>>
>>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>>> news:%23GYwu2$(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>
>