Excel SumProduct Function

Dear All,

I would like to use the Sumproduct Function of Excel in AFL or any alternative solution which can help, to get the summary from the row.

for example, as per the attached image, I would like to get the summary of OI for the respective Interpret Column any reference to lead would be helpful.

Untitled

regards

Deepak

See Add Summary Rows.

2 Likes

@TrendSurfer I do respect your input and I am also well aware of "how to ask a good question", I am also aware of the AddSummaryRows function of AFL, However Sumproduct is an Indexing function that picks the relevant value from one column based on criteria from other column and sums it.

Also as per the attached Screen Shot, if I am able to calculate the total of OI, I can easily get the Average, Min, Max, Stdev, etc, why will create the post for Sumproduct specifically.

The problem is with YOUR question as pointed out by @TrendSurfer. If you want to simulate Excel output, you have to provide full EXCEL formula (not just function name) and leave no place for guesses. You need to show your effort.

Generally, Excel SumProduct is merely multiplication and then sum and it can be done in AFL using combination multiplication and Sum()/Cum calls (sometimes IIF() and Sum() when SumProduct is used to replace conditional sum). Something like

Sum( array1 * array2, NumOfBars );

or

Cum( array1 * array2 );

But as I wrote, your question leaves a lot to be desired and forces guessing what you want, and answers based on guesses are a) time consuming b) difficult c) rarely correct. So, if you want to get correct answer, TRULY follow advice this advice: How to ask a good question

2 Likes

@Tomasz, thanks for the revert, and yes will try to be more clear henceforth, as you rightly suggested the cum () function to get the desired result of Sumproduct, however, I was stuck due to 5th Column in my Afl Screenshot was "String" and the 9th Column is number, hence I was not been able to solve this riddle but will try as per your suggestion and update.

Again, post your CODE. We don't know what columns , strings or whatever you have unless you post the CODE.

1 Like

@Tomasz It works, Thanks.

PutLongBuildup = Cum((vPOIC>0 && VPutLTPchg>0)*vPutOI);

ABC

1 Like

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.