CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulasumifssumproductnamed-function
Kim Hopkins
Kim Hopkins

Reputation: 85

How can I sum a named function over a given range of values?

I'm making a financial analysis model for real estate properties. I created a named function called INCOME that calculates total income for a given property name from a database of different income types for each property, e.g. rent, late fees, misc. income, etc.

For example, if you type in =INCOME("Property A"), it will calculate the total income for Property A from the database.

Now I'm trying to calculate Income (along with a lot of other more complicated functions) across the entire portfolio of properties.

So I want to make a list of all the properties, say Property A, Property B, and Property C, then I want to calculate the sum of the incomes using my INCOME function. See snippet below:

enter image description here

But I want this to be dynamic. So what I really want is to enter a formula that says: take the sum of INCOME(B5) to INCOME(Bn) where Bn is the last non-empty row, i.e. the last property in the Properties column. This way it will update correctly as I add more properties.

Is it possible to do this in Google Sheets (without using Apps Script)?

Upvotes: 0

Views: 425

Answers (3)

horanimal
horanimal

Reputation: 424

  1. Enter “Properties” in B4, then in B5 enter:
=SORT(UNIQUE(Sheet1!A2:A))

Where you replace “Sheet1!A2:A” with whatever sheet & column has your property names, to get a dynamic list of all your properties.

  1. Enter “Income” in C4, then in C5 enter:
=BYROW(B5:B,LAMBDA(r,
    IF(r=“”,””,
    INCOME(r))))

Which will go through and run your custom INCOME function for every property in your dynamically populated properties list.

  1. In C3 enter =SUM(C5:C) which will give you the total income for all your properties dynamically and will remain in the same cell no matter how long or short that property list gets.

  2. Repeat steps 2 & 3 in the columns to the right for however many custom formulas/metrics you need!

Upvotes: 0

Thomas
Thomas

Reputation: 94

Tried it just for sport!

example

Let's say we have the custom function INCOME bellow :

function INCOME( value ) {
  
    return 2
}

Using REDUCE and LAMBDA with the GS formula bellow seems to be enough to get the sum you want

reduce(0;B5:B;lambda(a;c;if(c="";a;a+INCOME(c))))

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 29904

You may try this & see how it goes:

=let(Σ,tocol(B5:B,1),
       reduce(,sequence(counta(Σ)),lambda(a,c,a+index(INCOME(Σ),c))))

Upvotes: 2

Related Questions