conditional reference

Dear all,

is there a way in Excel to get a range of cells as the result of a
conditional function? Like the SUMIF function, but without summing the cells
(only their reference)
Say that I want to use all the values in column B for which the cells in
column A="a". The result in cell C1 should be the range B1:B4.
Is this possible?
A B C
a 11 if A1:A7="a" --> range (=B1:B4)
a 12
a 10
a 11
b 8
b 8
b 9

You can use SUMPRODUCT()

Try this:

=SUMPRODUCT(--(A1:A7="a"),B1:B7) which gives 44 using your example

=sumif(A1:A7,"a",B1:B7)

or

=sumproduct(--(A1:A7="a"),B1:B7)

"Maarten" wrote:

> Dear all,
>
> is there a way in Excel to get a range of cells as the result of a
> conditional function? Like the SUMIF function, but without summing the cells
> (only their reference)
> Say that I want to use all the values in column B for which the cells in
> column A="a". The result in cell C1 should be the range B1:B4.
> Is this possible?
> A B C
> a 11 if A1:A7="a" --> range (=B1:B4)
> a 12
> a 10
> a 11
> b 8
> b 8
> b 9

Hi, try this:

="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))+COUNTIF(A:A,"a")-1

or

="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))+COUNTIF(A:A,F1)-1

where F1 is your lookup value.

HTH
JG

I don't understand why SUMIF doesn't work

=SUMIF(A1:A7,"a",B1:B7)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Maarten" <[email protected]> wrote in message
news:[email protected]...
> Dear all,
>
> is there a way in Excel to get a range of cells as the result of a
> conditional function? Like the SUMIF function, but without summing the
cells
> (only their reference)
> Say that I want to use all the values in column B for which the cells in
> column A="a". The result in cell C1 should be the range B1:B4.
> Is this possible?
> A B C
> a 11 if A1:A7="a" --> range (=B1:B4)
> a 12
> a 10
> a 11
> b 8
> b 8
> b 9

I do. Apparently Pinmaster was the only one of us to properly read the OP's request.

Sorry 'bout that Maarten.

Great, that works, but it seems that I can't do any calculations with the
result. It looks like Excel doesn't recognize the formula result as a
reference. Is there any way to get around with this?
I want to use the results (reference) in a formula ('bootmean' from Poptools
addin)which may only use cells in column B from a certain class (defined in
column A).

Is there a way to make Excel recognize the result of the formula below as a
reference?

Many thanks,
Maarten

"pinmaster" wrote:

>
> Hi, try this:
>
> ="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))+COUNTIF(A:A,"a")-1
>
> or
>
> ="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))+COUNTIF(A:A,F1)-1
>
> where F1 is your lookup value.
>
> HTH
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=494109
>
>

Could you not simply INDIRECT it and use that in the PopTools formula. For
instance, if this formula returns B1:B10, =SUM(INDIRECT(this_formula)) will
sum those cells.

Other than that, you could use an array formula to get a range, like so

=SUM(OFFSET(B1,,,COUNTA(IF(A1:A100="a",A1:A100)),1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Maarten" <[email protected]> wrote in message
news:[email protected]...
> Great, that works, but it seems that I can't do any calculations with the
> result. It looks like Excel doesn't recognize the formula result as a
> reference. Is there any way to get around with this?
> I want to use the results (reference) in a formula ('bootmean' from
Poptools
> addin)which may only use cells in column B from a certain class (defined
in
> column A).
>
> Is there a way to make Excel recognize the result of the formula below as
a
> reference?
>
> Many thanks,
> Maarten
>
> "pinmaster" wrote:
>
> >
> > Hi, try this:
> >
> >
="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH("a",A:A,0)
))+COUNTIF(A:A,"a")-1
> >
> > or
> >
> >
="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))
+COUNTIF(A:A,F1)-1
> >
> > where F1 is your lookup value.
> >
> > HTH
> > JG
> >
> >
> > --
> > pinmaster
> > ------------------------------------------------------------------------
> > pinmaster's Profile:
http://www.excelforum.com/member.php...fo&userid=6261
> > View this thread:
http://www.excelforum.com/showthread...hreadid=494109
> >
> >

Thanks a lot!

"Bob Phillips" wrote:

> Could you not simply INDIRECT it and use that in the PopTools formula. For
> instance, if this formula returns B1:B10, =SUM(INDIRECT(this_formula)) will
> sum those cells.
>
> Other than that, you could use an array formula to get a range, like so
>
> =SUM(OFFSET(B1,,,COUNTA(IF(A1:A100="a",A1:A100)),1))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Maarten" <[email protected]> wrote in message
> news:[email protected]...
> > Great, that works, but it seems that I can't do any calculations with the
> > result. It looks like Excel doesn't recognize the formula result as a
> > reference. Is there any way to get around with this?
> > I want to use the results (reference) in a formula ('bootmean' from
> Poptools
> > addin)which may only use cells in column B from a certain class (defined
> in
> > column A).
> >
> > Is there a way to make Excel recognize the result of the formula below as
> a
> > reference?
> >
> > Many thanks,
> > Maarten
> >
> > "pinmaster" wrote:
> >
> > >
> > > Hi, try this:
> > >
> > >
> ="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH("a",A:A,0)
> ))+COUNTIF(A:A,"a")-1
> > >
> > > or
> > >
> > >
> ="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))
> +COUNTIF(A:A,F1)-1
> > >
> > > where F1 is your lookup value.
> > >
> > > HTH
> > > JG
> > >
> > >
> > > --
> > > pinmaster
> > > ------------------------------------------------------------------------
> > > pinmaster's Profile:
> http://www.excelforum.com/member.php...fo&userid=6261
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=494109
> > >
> > >
>
>
>