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

 

[Tetra201]

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

Please re-check your calculations (771 and 1276) -- they are inconsistent.

Then try one of these formulas:
=SUMIFS(Blocks!$B$2:$B$16,Blocks!$A$2:$A$16,">="&$A3,Blocks!$A$2:$A$16,"<"&$B3)
=SUMIFS(Blocks!$B$2:$B$16,Blocks!$A$2:$A$16,">="&$A3,Blocks!$A$2:$A$16,"<="&$B3)

 

[palaeontology]

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

alternatively, you could try ...

=SUM(OFFSET(A1,MATCH(D2,$A$1:$A$15)-1,1):OFFSET(A1,MATCH(E2,$A$1:$A$15)-1,1))

where D2 holds the start point and E2 holds the finish point

you'd need to change $A$1:A$15 to whatever range houses your 'section of farm' values

Kind regards,

Chris

 

[Tiptop999]

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

Thanks for your help Tetra. The second suggestion worked.

Much appreciated,

Mike

 

[Tiptop999]

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

Thanks for your suggestion Palaeontology. I appreciate your help.

 

[Tiptop999]

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

Hi Palaeontology & Tetra,

As stated above your suggestions worked, initially. I had a few days out the office and only got a quick chance to put your formula through there paces before leaving. Upon my return I tested them more thoroughly and found they didn't work completely. Both worked until the 'section of farm' values increased passed 10. So until A9.9 everything worked but when it went above that to A10.1 they either read 0 or N/A.

Would you have any suggestions as to what went wrong?

Regards Mike

 

[palaeontology]

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

Easy fix Mike, try this ...

=SUM(OFFSET(A1,MATCH(D2,$A$1:$A$16,0)-1,1):OFFSET(A1,MATCH(E2,$A$1:$A$16,0)-1,1))

where D2 holds the start point and E2 holds the finish point

again, you'd need to change $A$1:A$16 to whatever range houses your 'section of farm' values

Kind regards,

Chris

 

[Tiptop999]

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

Hi Chris,

That worked a treat thanks. Very much appreciate your time and help,

Kind regards,

Mike

 

[Tiptop999]

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

Chris, maybe you can help with another issue I have,

Is effectively the same question as above however than adding id like to simply count the inputs. Below is what I've been experimenting with, which works great for other applications (such as counting a column between 2 dates) but doesn't work so well for what I need.

=COUNTIFS($A$1:$A$16,">="&D2,$A$1:$A$16,"<="&E2)

Regards,

Mike

 

[palaeontology]

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

Not sure if I'm reading your request correctly.

Do you want to count the number of rows within the range reference ... for example ... A1.3 to A2.2 covers a span of 5 rows.

If that is what you want, then use ...

=ROWS(OFFSET(A1,MATCH(D2,$A$1:$A$16,0)-1,0):OFFSET(A1,MATCH(E2,$A$1:$A$16,0)-1,0))

Kind regards,

Chris