• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to return the sum of functions dynamically in excel

S

Shanky

New Member
I have the following table in my excel.

screen1-jpg.830


and I have the VBA function (fcComm) to calculate the some number with reference values as in =fcComm(A7,B7,D7,F7,"2:3").
For each row (for eg, Row 7) I will have values automatically generated in Scores table (from row 11 to 16).


But I have a scenario where I need to add more number of rows above row 10. and I would want my function to automatically give me the sum of results of function
For example,
If I have 3 players (40, 41, 42) in row 7, 8 and 9, i want the following value in E13.

=SUM(fcComm(A7,B7,D7,F7,"2:3") + fcComm(A8,B8,D8,F8,"2:3") + fcComm(A9,B9,D9,F9,"2:3")).

Is there any function in excel to sum these values dynamically ?

Note: I am using =SUM(E7:OFFSET(E10,-1,0)) for some other function in the same sheet, but this particular table is bit unique and I am not sure how to use this.
 

Attachments

  • screen1.jpg
    screen1.jpg
    42.8 KB · Views: 20
Can't you use those individual functions in their corresponding rows, and the use a sum function like this?

=SUM(E7:E9)

Maybe I'm not understanding your question right.
 
Thanks for your reply Sam.
But my requirement is different.

In the image, E13 would result some value based on user input in the row 7.
My requirement is to automatically sum up the results that my function returns when new row added after row 8.
For ex, =sum(function return(row7), function return(row8)).

Apologize, If my way of illustrating the problem confused you.
 
And I cannot add those function for each row, in which case, I need to copy entire Scores table (row 11-16) for each row !!!
 
Hi ,

You need to have a range as dynamic only if you have no idea as to how much it can grow.

If you can put an upper limit on the number of rows that will be needed , then all you have to do is allow those many rows at the top above your Scores table , and take all those rows into your formulae within the Scores table.

The only requirement is that your function fcComm should return 0 when it sees parameters which are either blank or 0.

Narayan
 
You can do it this way. And keep on inserting rows (up to 100 rows in my example below, but you can increase that)

=SUM(OFFSET(E7,,,MATCH("Scores",A7:INDIRECT("A106"),),))
 
@Hui Uploaded the file as requested.
Table is in sheet 5 (Forecast_CS).

Thanks,
Shanky
 

Attachments

  • TM.xlsm
    112.6 KB · Views: 4
Shanky

I think you'd be better to have the FcComm function rewritten to do this as a function

that way you could do something like:
=fcComm(A14:A17,B14,D14,F14,"0:2")
The A14:A17 would tell the function to do this for the three rows and add the results
 
Hi Sam ,

I think what the OP is looking for is a way to use the additional rows within the function fcComm ;

For example , if there were only one row in the section labelled Forecast , then the formulae within the Scores section would be as follows :

=fcComm($A$2,$B$2,$D$2,$F$2,$A7&":"&B$6)

This would be entered in B7 , and copied down and across.

If there are two rows of data in the section labelled Forecast , then the formulae within the Scores section would be as follows :

=fcComm($A$2,$B$2,$D$2,$F$2,$A7&":"&B$6)+fcComm($A$3,$B$3,$D$3,$F$3,$A7&":"&B$6)

If the data now extends to three rows of data in the section labelled Forecast , then the formulae within the Scores section would be as follows :

=fcComm($A$2,$B$2,$D$2,$F$2,$A7&":"&B$6)+fcComm($A$3,$B$3,$D$3,$F$3,$A7&":"&B$6)+fcComm($A$4,$B$4,$D$4,$F$4,$A7&":"&B$6)

This dynamic expansion of the formula is what OP is looking for.

Narayan
 
You must log in or register to reply here.
Back
Top