Formula error - Column result based on multiple rows values

Question

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.

Link to Example Spreadsheet

Answer 1

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) 
      )  
    ) 
  ) 
)