SUMPRODUCT problem

[juliejg1]

I have the following formula:

=SUMPRODUCT(Data!$T$6ata!$T$5000="Long
Term")*SUM(Data!$BE$6ata!$BF$5000)/SUM(Data!$BE$6ata!$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)

 

[Bernard Liengme]

Close but not quite there. Try
=SUMPRODUCT((Data!$T$6ata!$T$5000="Long
Term")*(Data!$BE$6ata!$BF$5000))/SUM(Data!$BE$6ata!$BF$5000)
or
=SUMPRODUCT(--(Data!$T$6ata!$T$5000="Long
Term"),Data!$BE$6ata!$BF$5000)/SUM(Data!$BE$6ata!$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

 

[juliejg1]

Perfect! Thanks!

Close but not quite there. Try
=SUMPRODUCT((Data!$T$6ata!$T$5000="Long
Term")*(Data!$BE$6ata!$BF$5000))/SUM(Data!$BE$6ata!$BF$5000)
or
=SUMPRODUCT(--(Data!$T$6ata!$T$5000="Long
Term"),Data!$BE$6ata!$BF$5000)/SUM(Data!$BE$6ata!$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

 

[Don Guillett]

For future shorter formulas, you shouldn't need the second data!
=SUMPRODUCT((Data!$T$6ata!$T$5000="Long
=SUMPRODUCT((Data!$T$6:$T$5000="Long

 

[Bernard Liengme]

I didn't notice that! Good eyes, Don