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


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=494109

=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(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: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494109

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" wrote in message
...
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.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=494109

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(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: 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" wrote in message
...
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(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:
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" wrote in message
...
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(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:
http://www.excelforum.com/member.php...fo&userid=6261
View this thread:
http://www.excelforum.com/showthread...hreadid=494109