Melissa has attended:
Excel PowerPivot course
SumProduct
Hello,
I am looking for an alternative to the following sumproduct formula which I am using, below. I am using office pro plus 2013
I am trying to calculate, for each day the sum of all (Current Price-Opening Price)*Quantity. For each name there is a different open and current price, the closing date needs to be in the future and the open date needs to either be the date that is being reviewed or in the past.
=SUMPRODUCT(--(Sheet1[Close Date]>'Sheet2'!Date)*--(Sheet1[Open Date]<='Sheet2'!Date)*--(Sheet1[columnA]=NameA),('Sheet3'!B2-Sheet1[Opening Price]),(Sheet1[ComunC]))+SUMPRODUCT(--(Sheet1[Close Date]>'Sheet2'!A2)*--(Sheet1[Open Date]<='Sheet2'!A2)*--(Sheet1[columnA]=NameB),('Sheet3'!Current Price-Sheet1[Opening Price]),(Sheet1[ComunC]))....
I am running into a problem as I can have 30+ Names on one sheet, and there becomes to many characters for excel. The names are located in the first row of sheet 3, each name has a corresponding price for each day. The Names relate to sheet 1 Column A.
Any help would be appreciated.
Thank you
RE: SumProduct
Hello Melissa,
Hope you enjoyed your Microsoft Excel PowerPivot course with Best STL.
Thank you for your question regarding using the SUMPRODUCT function.
It will easier if I have a sample of your file. Please replace names and other confidential data with sample data and send to me at:
rl@stl-training.co.uk
I look forward to hearing from you.
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: SumProduct
Thanks Rodney, I sent you an email yesterday subject SumProduct Help.
Looking forward to hearing from you
Melissa
RE: SumProduct
Hello Melissa,
We are clearing out any potentially unresolved forum questions, so just checking to see if you got this question resolved.
If not, please would you send your file to forum@stl-training.co.uk and we will take a look at it again for you.
Kind Regards,
Richard
