Adding Meters In One Column Based On Areas In Another

[Tiptop999]

Hi everybody,

Recently we moved over from Google Sheets to Excel. I'mrelatively familiar with Excel but more so with Sheets. In the process oftransferring a few workbooks I found some formula and functions didn't workanymore. Most I have been able to correct but one is causing me a headache. Theformula in question was created by a colleague I know longer work with. I don’treally know how it works in sheets let alone trying to replicate it in Excel.
What I’m trying to accomplish is adding areastogether based on a start and stop point. I work on a farm, at times we need toknow how many meters there are between point A and point B. The blocks of landare broken down into Letters and Numbers.

Table 1,

It is worth noting that the sections on our farm go from A to S,
with 4,500 individual lengths in meters.
Example 1, is just a small grab to show how we work.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Section Of Farm [/TD]
[TD]Meters [/TD]
[/TR]
[TR]
[TD]A1.1[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]A1.2[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]A1.3[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]A1.4[/TD]
[TD]134[/TD]
[/TR]
[TR]
[TD]A1.5[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]A2.1[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]A2.2[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]A2.3[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A2.4[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A2.5[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]B1.1[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]B1.2[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]B1.3[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]B1.4[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]B1.5[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]


In the Below table I have shown how I would like things to work. Column A the start point B the End and C everything added together between those 2 points. Put simply if we was to do this manually, looking through Table 1, column A to find A1.1, then A2.2. Finally adding all the meters together from Table 1 column B between those two points in column A.

Table 2,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Stop[/TD]
[TD]Meters[/TD]
[/TR]
[TR]
[TD]A1.1[/TD]
[TD]A2.2[/TD]
[TD]771[/TD]
[/TR]
[TR]
[TD]A2.1[/TD]
[TD]B1.5[/TD]
[TD]1276[/TD]
[/TR]
</tbody>[/TABLE]


Below is the Working formula from Google Sheets, I understand its the filter function which is causing the problems, as Excel doesn't have it. I just cannot figure out how to make this work without it.


=if(sum( iferror( filter(Blocks!$B$1:$B, match($A3, Blocks!$A:$A, 0) <= row(Blocks!$B$1:$B), row(Blocks!$B$1:$B) <= match($B3, Blocks!$A:$A, 0) ) ) )/5800=0,"",(sum( iferror( filter(Blocks!$B$1:$B, match($A3, Blocks!$A:$A, 0) <= row(Blocks!$B$1:$B), row(Blocks!$B$1:$B) <= match($B3, Blocks!$A:$A, 0) ) ) )/5800))

Any help in either solving the problem or help in overcoming it would be greatly appreciated,

Mike

 

[Tiptop999]

Re: Adding Meters In One Column Based On Areas In Another, Help!

Hi Chris,

Apologies for the poor explanation but you nailed it.

Thanks again,

Mike