|
SUMPRODUCT problem
I have the following formula:
=SUMPRODUCT(Data!$T$6:Data!$T$5000="Long Term")*SUM(Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) which produces the wrong answer. Column T contains "Long Term", "Short Term", "Spot" Column BE contains spend for January, BF for February, BG for March, etc... I would like to calculate what percentage of year to date total spend there is for "long term" contracts for February. This calculation would be: (Total "long term" spend for Jan and Feb) / (Total spend year to date) |
SUMPRODUCT problem
Close but not quite there. Try
=SUMPRODUCT((Data!$T$6:Data!$T$5000="Long Term")*(Data!$BE$6:Data!$BF$5000))/SUM(Data!$BE$6:Data!$BF$5000) or =SUMPRODUCT(--(Data!$T$6:Data!$T$5000="Long Term"),Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "juliejg1" wrote in message ... I have the following formula: =SUMPRODUCT(Data!$T$6:Data!$T$5000="Long Term")*SUM(Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) which produces the wrong answer. Column T contains "Long Term", "Short Term", "Spot" Column BE contains spend for January, BF for February, BG for March, etc... I would like to calculate what percentage of year to date total spend there is for "long term" contracts for February. This calculation would be: (Total "long term" spend for Jan and Feb) / (Total spend year to date) |
SUMPRODUCT problem
Perfect! Thanks!
"Bernard Liengme" wrote: Close but not quite there. Try =SUMPRODUCT((Data!$T$6:Data!$T$5000="Long Term")*(Data!$BE$6:Data!$BF$5000))/SUM(Data!$BE$6:Data!$BF$5000) or =SUMPRODUCT(--(Data!$T$6:Data!$T$5000="Long Term"),Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "juliejg1" wrote in message ... I have the following formula: =SUMPRODUCT(Data!$T$6:Data!$T$5000="Long Term")*SUM(Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) which produces the wrong answer. Column T contains "Long Term", "Short Term", "Spot" Column BE contains spend for January, BF for February, BG for March, etc... I would like to calculate what percentage of year to date total spend there is for "long term" contracts for February. This calculation would be: (Total "long term" spend for Jan and Feb) / (Total spend year to date) |
SUMPRODUCT problem
I didn't notice that! Good eyes, Don
-- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Don Guillett" wrote in message ... For future shorter formulas, you shouldn't need the second data! =SUMPRODUCT((Data!$T$6:Data!$T$5000="Long =SUMPRODUCT((Data!$T$6:$T$5000="Long -- Don Guillett Microsoft MVP Excel SalesAid Software "juliejg1" wrote in message ... Perfect! Thanks! "Bernard Liengme" wrote: Close but not quite there. Try =SUMPRODUCT((Data!$T$6:Data!$T$5000="Long Term")*(Data!$BE$6:Data!$BF$5000))/SUM(Data!$BE$6:Data!$BF$5000) or =SUMPRODUCT(--(Data!$T$6:Data!$T$5000="Long Term"),Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "juliejg1" wrote in message ... I have the following formula: =SUMPRODUCT(Data!$T$6:Data!$T$5000="Long Term")*SUM(Data!$BE$6:Data!$BF$5000)/SUM(Data!$BE$6:Data!$BF$5000) which produces the wrong answer. Column T contains "Long Term", "Short Term", "Spot" Column BE contains spend for January, BF for February, BG for March, etc... I would like to calculate what percentage of year to date total spend there is for "long term" contracts for February. This calculation would be: (Total "long term" spend for Jan and Feb) / (Total spend year to date) |
| All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com