Ok, I have a table that is looking to sum the relevant sections, the
table currently goes:

0-10,000
10,001-20,000
20,001-30,000

And so on (all figures are integers). The ranges relate to turnover by
case and for whichever range the turnover falls in I want to sum a cost
column. This is simple enough to do and at the moment I have:

=SUMIF($E$13:$E$17,"0",$F$3:$F$5000)-SUMIF($E$3:$E$5000,"10000",$F$3:$F$5000)

The problem is this range is liable to change and I want to link the
greater than (and less than) to the cells the figures fit in. Each of
the components of the range (i.e. the 0-10000) is in a different cell
so easy to link to but Excel isn’t having it. I have tried using
INDIRECT and CONCATENATE to get it to work but it still isn't working –
any ideas?


--
chalky
------------------------------------------------------------------------
chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=493048

Something like

=SUMIF($E$13:$E$17,""&A1,$F$3:$F$5000)-SUMIF($E$3:$E$5000,"",&B1,$F$3:$F$5
000)

where A1 and B1 hold the values


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"chalky" wrote in
message ...

Ok, I have a table that is looking to sum the relevant sections, the
table currently goes:

0-10,000
10,001-20,000
20,001-30,000

And so on (all figures are integers). The ranges relate to turnover by
case and for whichever range the turnover falls in I want to sum a cost
column. This is simple enough to do and at the moment I have:


=SUMIF($E$13:$E$17,"0",$F$3:$F$5000)-SUMIF($E$3:$E$5000,"10000",$F$3:$F$50
00)

The problem is this range is liable to change and I want to link the
greater than (and less than) to the cells the figures fit in. Each of
the components of the range (i.e. the 0-10000) is in a different cell
so easy to link to but Excel isn't having it. I have tried using
INDIRECT and CONCATENATE to get it to work but it still isn't working -
any ideas?


--
chalky
------------------------------------------------------------------------
chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=493048

Hi

Try
=SUMIF($E$13:$E$17,""&A1,$F$3:$F$5000)-SUMIF($E$3:$E$5000,""&A2,$F$3:$F$5000)

Where A1 and A2 hold your respective values

Regards

Roger Govier


chalky wrote:
Ok, I have a table that is looking to sum the relevant sections, the
table currently goes:

0-10,000
10,001-20,000
20,001-30,000

And so on (all figures are integers). The ranges relate to turnover by
case and for whichever range the turnover falls in I want to sum a cost
column. This is simple enough to do and at the moment I have:

=SUMIF($E$13:$E$17,"0",$F$3:$F$5000)-SUMIF($E$3:$E$5000,"10000",$F$3:$F$5000)

The problem is this range is liable to change and I want to link the
greater than (and less than) to the cells the figures fit in. Each of
the components of the range (i.e. the 0-10000) is in a different cell
so easy to link to but Excel isn’t having it. I have tried using
INDIRECT and CONCATENATE to get it to work but it still isn't working –
any ideas?

Ah, perfect. I couldn't see the wood for the trees on that one, thanks
for your help.

Chris


--
chalky
------------------------------------------------------------------------
chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
View this thread: http://www.excelforum.com/showthread...hreadid=493048