Creating a summary Page

I am a surveyor and am currently working out quantities of pipework etc.

I have an electronic take off sheet that works out the various lengths and
depths and sizes of pipe. I am trying to create a summary page for this.
There are several different pipe sizes from 150, 200, 225, 250 etc.

But i need to summarise these to various lengths and pipe sizes into depth
ranges. So say for every length of 150 pipe at 1m to 1.5m in depth i need it
to sum up the total length on a separate sheet within the same workbook.

I have played about wth array formulas etc but with little success

Can anyone help?

Any help would be greatly appreciated

Thankyou
Grant

--
Message posted via http://www.officekb.com

Grant,

If i understand your question right, is your problem that you have several
lenght's of pipe's. And you want to know what the total size is of the used
pipes per pipe size.

I think that you can resolve this by using the formula (a) sumif() or (b)
countif() and multiply this with the size per pipe.

Example (a)
=Sumif(Range where you see the used size;the size u want to summarize;range
which can summarize)

Example (b)
=Countif(Range where you see the used size;the size u want to
summarize;range which can summarize)*1,50 or b1, etc

Good luck and if this doesn't solve your problem I'll read it here.

Greetings

Stefan

"Newbie81 via OfficeKB.com" wrote:

> I am a surveyor and am currently working out quantities of pipework etc.
>
> I have an electronic take off sheet that works out the various lengths and
> depths and sizes of pipe. I am trying to create a summary page for this.
> There are several different pipe sizes from 150, 200, 225, 250 etc.
>
> But i need to summarise these to various lengths and pipe sizes into depth
> ranges. So say for every length of 150 pipe at 1m to 1.5m in depth i need it
> to sum up the total length on a separate sheet within the same workbook.
>
> I have played about wth array formulas etc but with little success
>
> Can anyone help?
>
> Any help would be greatly appreciated
>
> Thankyou
> Grant
>
> --
> Message posted via http://www.officekb.com
>

The trouble is that there is a third argument to the formula i need to add in
and that is the depth range.

The average depth of each pipe is calculated and needs to be summarised not
only in size of pipe but also by the depth range it falls into. I need it to
look something like this:
Depth Ranges
Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
150 Length? Length? Length?
200 Length? Length? Length?

I have tried various formulas the one below is the best so far. Trouble is it
picks up all of the lengths at every depth range
{=SUM(IF(('Foul runs'!$E$6:$E$100>1)+('Foul runs'!$E$6:$E$100<1.5),'Foul
runs'!U$6:U$100))}

On the foul runs page i have got it to split out the various sizes into
different columns using an 'IF' formula ( Would be better if i could
incorperate this in the summary formula). So basically i need to add an
argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
sum the length.

Can you help?

Thanks Grant
Stefan wrote:
>Grant,
>
>If i understand your question right, is your problem that you have several
>lenght's of pipe's. And you want to know what the total size is of the used
>pipes per pipe size.
>
>I think that you can resolve this by using the formula (a) sumif() or (b)
>countif() and multiply this with the size per pipe.
>
>Example (a)
>=Sumif(Range where you see the used size;the size u want to summarize;range
>which can summarize)
>
>Example (b)
>=Countif(Range where you see the used size;the size u want to
>summarize;range which can summarize)*1,50 or b1, etc
>
>Good luck and if this doesn't solve your problem I'll read it here.
>
>Greetings
>
>Stefan
>
>> I am a surveyor and am currently working out quantities of pipework etc.
>>
>[quoted text clipped - 14 lines]
>> Thankyou
>> Grant

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Hi grant,

I have a solution for you.
Your problem can be resolved by using the following format:

Worksheet Foul runs
A B C ===> Columns,
vertical the rows ;-)
length size Length & size
1,25 150 1.501,25 (formula: =value(B2&A2)
1,25 150 1.501,25
1,30 150 1.501,30
1,65 150 1.501,65
1,70 150 1.501,70
1,25 200 2.001,25
1,40 200 2.001,40
1,65 200 2.001,65
1,65 200 2.001,65
1,80 200 2.001,80
14,90

Explanation formula
I need to combine the numbers for making the formula.
Because the format of the numbers is text when you use the & function, I had
to make it values by the function value.

Worksheet summary:

A B C D
E F ==> Columns
1,00 1,50 1,50 2,00 Total
150 3,80 3,35 7,15
200 2,65 5,10 7,75
14,90

I have merge the cells B2&C2 by cell properties. I did this als for B3&C3,
D2&E2, etc.
I did this, because in mine formula I am going to use the headers of this
summary (rows and colums).
The Formula for cel B2 (merged cell b2&c2) is as followed)

=SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&B$3);'Foul
Runs'!$A$2:$A$11)-SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&C$3);'Foul
Runs'!$A$2:$A$11)

This formula can you copy into the summary thanks to the $-sign.
But I'll explain the formula also.

In this formula I say:
Check if column C > 1501 (formula VALUE($A4&B$3));than take the sum of all
value which are bigger - (minus) check if column C >1501,50; than take the
sum of all value which are bigger.

If you need more explanation I'll read it on the site.
Good luck

Stefan

"Newbie81 via OfficeKB.com" wrote:

> The trouble is that there is a third argument to the formula i need to add in
> and that is the depth range.
>
> The average depth of each pipe is calculated and needs to be summarised not
> only in size of pipe but also by the depth range it falls into. I need it to
> look something like this:
> Depth Ranges
> Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
> 150 Length? Length? Length?
> 200 Length? Length? Length?
>
> I have tried various formulas the one below is the best so far. Trouble is it
> picks up all of the lengths at every depth range
> {=SUM(IF(('Foul runs'!$E$6:$E$100>1)+('Foul runs'!$E$6:$E$100<1.5),'Foul
> runs'!U$6:U$100))}
>
> On the foul runs page i have got it to split out the various sizes into
> different columns using an 'IF' formula ( Would be better if i could
> incorperate this in the summary formula). So basically i need to add an
> argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
> sum the length.
>
> Can you help?
>
> Thanks Grant
> Stefan wrote:
> >Grant,
> >
> >If i understand your question right, is your problem that you have several
> >lenght's of pipe's. And you want to know what the total size is of the used
> >pipes per pipe size.
> >
> >I think that you can resolve this by using the formula (a) sumif() or (b)
> >countif() and multiply this with the size per pipe.
> >
> >Example (a)
> >=Sumif(Range where you see the used size;the size u want to summarize;range
> >which can summarize)
> >
> >Example (b)
> >=Countif(Range where you see the used size;the size u want to
> >summarize;range which can summarize)*1,50 or b1, etc
> >
> >Good luck and if this doesn't solve your problem I'll read it here.
> >
> >Greetings
> >
> >Stefan
> >
> >> I am a surveyor and am currently working out quantities of pipework etc.
> >>
> >[quoted text clipped - 14 lines]
> >> Thankyou
> >> Grant
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200601/1
>

som = sum

"Stefan" wrote:

> Hi grant,
>
> I have a solution for you.
> Your problem can be resolved by using the following format:
>
> Worksheet Foul runs
> A B C ===> Columns,
> vertical the rows ;-)
> length size Length & size
> 1,25 150 1.501,25 (formula: =value(B2&A2)
> 1,25 150 1.501,25
> 1,30 150 1.501,30
> 1,65 150 1.501,65
> 1,70 150 1.501,70
> 1,25 200 2.001,25
> 1,40 200 2.001,40
> 1,65 200 2.001,65
> 1,65 200 2.001,65
> 1,80 200 2.001,80
> 14,90
>
> Explanation formula
> I need to combine the numbers for making the formula.
> Because the format of the numbers is text when you use the & function, I had
> to make it values by the function value.
>
> Worksheet summary:
>
> A B C D
> E F ==> Columns
> 1,00 1,50 1,50 2,00 Total
> 150 3,80 3,35 7,15
> 200 2,65 5,10 7,75
> 14,90
>
> I have merge the cells B2&C2 by cell properties. I did this als for B3&C3,
> D2&E2, etc.
> I did this, because in mine formula I am going to use the headers of this
> summary (rows and colums).
> The Formula for cel B2 (merged cell b2&c2) is as followed)
>
> =SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&B$3);'Foul
> Runs'!$A$2:$A$11)-SOM.IF('Foul Runs'!$C$2:$C$11;">"&VALUE($A4&C$3);'Foul
> Runs'!$A$2:$A$11)
>
> This formula can you copy into the summary thanks to the $-sign.
> But I'll explain the formula also.
>
> In this formula I say:
> Check if column C > 1501 (formula VALUE($A4&B$3));than take the sum of all
> value which are bigger - (minus) check if column C >1501,50; than take the
> sum of all value which are bigger.
>
> If you need more explanation I'll read it on the site.
> Good luck
>
> Stefan
>
> "Newbie81 via OfficeKB.com" wrote:
>
> > The trouble is that there is a third argument to the formula i need to add in
> > and that is the depth range.
> >
> > The average depth of each pipe is calculated and needs to be summarised not
> > only in size of pipe but also by the depth range it falls into. I need it to
> > look something like this:
> > Depth Ranges
> > Size of pipe 1.0 - 1.5 1.5 - 2.0 2.0 - 2.5
> > 150 Length? Length? Length?
> > 200 Length? Length? Length?
> >
> > I have tried various formulas the one below is the best so far. Trouble is it
> > picks up all of the lengths at every depth range
> > {=SUM(IF(('Foul runs'!$E$6:$E$100>1)+('Foul runs'!$E$6:$E$100<1.5),'Foul
> > runs'!U$6:U$100))}
> >
> > On the foul runs page i have got it to split out the various sizes into
> > different columns using an 'IF' formula ( Would be better if i could
> > incorperate this in the summary formula). So basically i need to add an
> > argument that says If the pipe is deeper then 1 but not deeper then 1.5 then
> > sum the length.
> >
> > Can you help?
> >
> > Thanks Grant
> > Stefan wrote:
> > >Grant,
> > >
> > >If i understand your question right, is your problem that you have several
> > >lenght's of pipe's. And you want to know what the total size is of the used
> > >pipes per pipe size.
> > >
> > >I think that you can resolve this by using the formula (a) sumif() or (b)
> > >countif() and multiply this with the size per pipe.
> > >
> > >Example (a)
> > >=Sumif(Range where you see the used size;the size u want to summarize;range
> > >which can summarize)
> > >
> > >Example (b)
> > >=Countif(Range where you see the used size;the size u want to
> > >summarize;range which can summarize)*1,50 or b1, etc
> > >
> > >Good luck and if this doesn't solve your problem I'll read it here.
> > >
> > >Greetings
> > >
> > >Stefan
> > >
> > >> I am a surveyor and am currently working out quantities of pipework etc.
> > >>
> > >[quoted text clipped - 14 lines]
> > >> Thankyou
> > >> Grant
> >
> > --
> > Message posted via OfficeKB.com
> > http://www.officekb.com/Uwe/Forums.a...excel/200601/1
> >