software

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=quot;aquot;. The result in cell C1 should be the range B1:B4.
Is this possible?
A B C
a 11 if A1:A7=quot;aquot; --gt; range (=B1:B4)
a 12
a 10
a 11
b 8
b 8
b 9


You can use SUMPRODUCT()

Try this:

=SUMPRODUCT(--(A1:A7=quot;aquot;),B1:B7) which gives 44 using your example--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=494109=sumif(A1:A7,quot;aquot;,B1:B7)

or

=sumproduct(--(A1:A7=quot;aquot;),B1:B7)

quot;Maartenquot; wrote:

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


Hi, try this:

=quot;Bquot;amp;ROW(INDEX(A:A,MATCH(quot;aquot;,A:A,0)))amp;quot;:quot;amp;quot;Bquot;amp;ROW( INDEX(A:A,MATCH(quot;aquot;,A:A,0))) COUNTIF(A:A,quot;aquot;)-1

or

=quot;Bquot;amp;ROW(INDEX(A:A,MATCH(F1,A:A,0)))amp;quot;:quot;amp;quot;Bquot;amp;ROW(I NDEX(A:A,MATCH(F1,A:A,0))) COUNTIF(A:A,F1)-1

where F1 is your lookup value.

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=494109I don't understand why SUMIF doesn't work

=SUMIF(A1:A7,quot;aquot;,B1:B7)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

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

Sorry 'bout that Maarten.--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=494109Great, 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

quot;pinmasterquot; wrote:

gt;
gt; Hi, try this:
gt;
gt; =quot;Bquot;amp;ROW(INDEX(A:A,MATCH(quot;aquot;,A:A,0)))amp;quot;:quot;amp;quot;Bquot;amp;ROW( INDEX(A:A,MATCH(quot;aquot;,A:A,0))) COUNTIF(A:A,quot;aquot;)-1
gt;
gt; or
gt;
gt; =quot;Bquot;amp;ROW(INDEX(A:A,MATCH(F1,A:A,0)))amp;quot;:quot;amp;quot;Bquot;amp;ROW(I NDEX(A:A,MATCH(F1,A:A,0))) COUNTIF(A:A,F1)-1
gt;
gt; where F1 is your lookup value.
gt;
gt; HTH
gt; JG
gt;
gt;
gt; --
gt; pinmaster
gt; ------------------------------------------------------------------------
gt; pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
gt; View this thread: www.excelforum.com/showthread...hreadid=494109
gt;
gt;

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=quot;aquot;,A1:A100)),1 ))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

quot;Bob Phillipsquot; wrote:

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