SumProduct

Resolved · Urgent Priority · Version 2007

Tue 9 Jul 2013, 15:44 Delegate Melissa said...

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

Tue 9 Jul 2013, 16:26 Trainer Rodney said...

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

Wed 10 Jul 2013, 09:06 Delegate Melissa said...

RE: SumProduct

Thanks Rodney, I sent you an email yesterday subject SumProduct Help.

Looking forward to hearing from you

Melissa

Mon 28 Oct 2013, 17:31 Trainer Richard said...

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

Mon 4 Nov 2013: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Change the default location for opening and saving spreadsheets

If you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to.

Here's how:
1. Go to Tools - Options.

2. Select the General tab.

3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).

4. Click OK.

You have now changed the default folder for opening and saving spreadsheets.

View all Excel hints and tips