SUMIF with a Greater Than
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 |
SUMIF with a Greater Than
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 |
SUMIF with a Greater Than
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? |
SUMIF with a Greater Than
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 |
All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com