# Formula error - Column result based on multiple rows values

Known values:

A B C D
Tenant Flat no. Duration stayed Energy consumed in flat

To find out:

E
Energy consumed per tenant

Condition:
I have to consider the unique value of column `A` as an identifier in order to avoid duplicate results/errors in calculations.

Example:
4 tenants `A2:A5` stay in the same flat `B2:B5`, they have consumed 85KW `D` over 30 days `C`

Simple Formula:
`=D2/SUM(C2:C5)*C2`. Manual process as per every flat

Formula to automate:

``````=IF(COUNTIF(\$A\$2:A2,A2)=1,D2/SUMIF(\$B\$2:\$B\$44,B2,\$C\$2:\$C\$44)*C2,"")
``````

However this formula only works up to a certain range. If I increase the range for column `B`, the result becomes incorrect.  The problem with your data is that there are several groups of data by the same flat number in column `B`, while only one group of data should be considered at a time. For example, flat `102` appears not only on rows 6, 7 and 8, but also in rows 91, 92, 93, 94 and 95.

The formula below will match the desired results you show. It creates a "window" to data that spans ten rows above and below the current cell, and only includes flats with a matching flat number within that window:

``````=let(
flats, B2:B, durations, C2:C, consumptions, D2:D,
subset_, lambda(flat,
sum(filter(
durations,
flats = flat,
isbetween(row(flats), row(flat) - 10, row(flat) + 10)
))
),
map(
flats, durations, consumptions,
lambda(
flat, duration, consumption,
if(
isblank(flat),
iferror(ø),
consumption * duration / subset_(flat)
)
)
)
)
``````
• Hi doubleunary, First of all, Thank you! This worked like a charm! Yes, they have common flat names in many cases which is why I wanted to find a way to consider the row in such a way that it could act as a unique identifier, in order to avoid miscalculations and give accurate results. I have updated the sheet link along with a column H, in which I used your code. I'm not good in coding but I understood the logic. Just curious though, can the same be done through google sheet functions without line of codes? Thanks again, much appreciated.
– Shiv
May 29 at 19:13
• The formula is using plain vanilla Google Sheets spreadsheet functions. It is just formatted with newlines and indentation to make it more readable. May 30 at 20:12