| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with SUMPRODUCT....help please! | Excel Worksheet Functions | |||
| Problem with SumProduct | Excel Discussion (Misc queries) | |||
| sumproduct problem? | Excel Discussion (Misc queries) | |||
| sumproduct problem | New Users to Excel | |||
| SUMPRODUCT problem | Excel Worksheet Functions | |||
Hybrid Mode
