Maybe your problem, or example problem, is a bit too simplified for folks. It's a perfectly valid spreadsheet problem. Let's assume that I posted this problem:
Given the letter in the header row of each column, how do I calculate its column count, inversly weighted by its row count? Consider the first header, B. It appears 4 times in that column, but each of those need to be inversly weighted by the number of times they appear in the row. (1/2) + (1/3) + (1/3) + (1/4)
| B | A | C | C | A | B | C | B | A | A |
|---|---|---|---|---|---|---|---|---|---|
| B | B | C | C | C | A | ||||
| C | C | A | A | B | |||||
| A | B | A | B | A | B | B | |||
| B | B | B | C | C | |||||
| A | B | C | A | B | |||||
| B | A | A | B | B | |||||
| B | B | A | C | B | B | C | |||
| B | B | A | A | C |
People would jump on this problem.
Your problem is very similar, but simplified in the fact that your rows all have the same person. and now we want the row count inversly weighted by the column count.
| 9/3 | 9/4 | 9/5 | 9/6 | 9/7 | 9/8 | 9/9 | 9/10 | 9/11 | 9/12 |
|---|---|---|---|---|---|---|---|---|---|
| bob | bob | bob | bob | ||||||
| bob | bob | bob | bob | bob | |||||
| larry | larry | larry | larry | larry | |||||
| bob | bob | bob | bob | bob | |||||
| larry | larry | larry | larry | ||||||
| chuck | chuck | chuck | chuck | ||||||
| chuck | chuck | chuck | chuck | chuck | |||||
| bob | bob | bob | bob | bob |
Here's a formula that will accomplish this task:
=let(
hrsPerDay,9,
hourlyRate,20,
startTimes,A2:A9,
endTimes,B2:B9,
names,C2:C9,
map(startTimes,endTimes,names,
lambda(start,end,name,
hrsPerDay*hourlyRate*sum(map(sequence(end-start+1,1,start),
lambda(t,1/sumproduct(names=name,startTimes<=t,endTimes>=t)))))))
Which gives us the final results.
| start | end | name | cost |
|---|---|---|---|
| 9/3/25 | 9/6/25 | bob | $390 |
| 9/4/25 | 9/8/25 | bob | $360 |
| 9/4/25 | 9/8/25 | larry | $540 |
| 9/5/25 | 9/9/25 | bob | $360 |
| 9/5/25 | 9/8/25 | larry | $360 |
| 9/5/25 | 9/8/25 | chuck | $630 |
| 9/8/25 | 9/12/25 | chuck | $810 |
| 9/8/25 | 9/12/25 | bob | $690 |