#### 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
```
 0
Newbie81
1/5/2006 4:29:09 PM
excel.misc 78881 articles. 5 followers.

4 Replies
284 Views

Similar Articles

[PageSpeed] 29

```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
>
```
 0
Stefan1 (43)
1/5/2006 5:24:02 PM
```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.aspx/ms-excel/200601/1
```
 0
Newbie81
1/6/2006 7:40:04 AM
```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.aspx/ms-excel/200601/1
>
```
 0
Stefan1 (43)
1/6/2006 11:53:02 AM
```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.aspx/ms-excel/200601/1
> >
```
 0
Stefan1 (43)
1/6/2006 11:56:06 AM

Similar Artilces:

cannot create profiles in win 7 mail 32 bit
I upgraded to WIndows 7 professional 64 bit and still cannot get my MS Office 2003 to work properly. All the programs except Outlook seems to work fine. I am totally unable to open outlook without mail profiles. I click on mail in control panel and I get low memoery or resources too low. That's impossible. I have 4GB RAM and 1GB VRAM. In addition, I noticed to night that while I created a profile for myself as Admin, I do ot have admin permissions EVEN IF I say 'run as admin" If I open my profile, it syas Admin under my name yet shows up as standard user. I...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Creating a letter from template
Is it possible in CRM? So far the only way i figured out to use templated is in an email and /KB-article. (And order offcourse) The thing i'm looking for is when selecting a contact, somehow i can make a letter activity and call a template that puts name & adress data etc. After that i should be able to print the letter, to put it in an enveloppe and send it someplace... So far it seems to me this cannot be done, but then again, i'm certainly not a CRM expert... Is it possible? If so, can anyone provide me the actions to be taken step by step ? thanks Kas "_Kas_&...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Creating a print-macro that'll only print page 1?
Hi, I have a spreadsheet that contains multiple sheets. I want to add a button that runs a macro, which only prints page 1 on the first sheet (called Scorekort). The "Scorekort" sheet contains 13 pages in total, but I only want to print the first page. Any ideas for this macro? Anders hi, Sub Macro1() Sheets("Scorekort").PrintOut From:=1, To:=1, Copies:=1, Preview:=True, Collate:=True End Sub if you don't want a print preview, Preview:=False -- isabelle Le 2011-05-21 04:16, Anders M a �crit : > Hi, > > I have a spreadsheet that contains multiple ...

Email accounts are not created for new user
I had a severe system crash several days ago because of bad memory in my domain controller (Server 2003 R2)/Exchange server 2003 (small site - no extra systems). I had to reinstall Exchange 2003 and reload the exchange store from backup. For existing users everything works great. Everybody can send and receive email, the calender works correctly, etc. OWA works. However, when I create a new user account in Active Directory Computers and Users, the account is created and the new user can log onto a system. However, Outlook 2003 cannot be configured to use the exchange server. In t...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

we do not create seperate http virtual server anymore?
Hi all, Just want to confirm that we do not need to create the seperate http virtual server for FE/BE (backend two node A/P Exchange 2003 cluster), right? I remember I need to create it in Exchange 2000 FE/BE clustering envir. Thanks in advance. On Wed, 2 Aug 2006 11:44:26 -0700, John <John@discussions.microsoft.com> wrote: >Hi all, > >Just want to confirm that we do not need to create the seperate http virtual >server for FE/BE (backend two node A/P Exchange 2003 cluster), right? I >remember I need to create it in Exchange 2000 FE/BE clustering envir. > >...

Error .. "Could not complete operation. One or more parameter values are not vaild" ...

View as web page in OWA
in OWA 2002, i remember there is a feature "View as web page" on the right side of the email. in OWA 2003, after open the received email, such feature no longer there. Anyway to enable this feature? ...

create main configuration file for solution.
Hello. How can I create main configuration file for my solution (Win-Form : VS 2008. VB.NET), like a general ini-file. Thanks :) "Mr. X." wrote: > Hello. > How can I create main configuration file for my solution (Win-Form : VS > 2008. VB.NET), like a general ini-file. > It's called an app.config. There is a name space you can use to mainulupate the app.config from a program. I see it on the project source directory (not for solution). I cannot see it on the release directory. Is there any convention to read / write from app.config...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

WdfUsbTargetDeviceCreate creates NULL Control Pipe Handle
Hi, We have a usb composite device which has one mass storage interface and another as a network interface. We are developing a WDF driver (NDIS-USB) for the network interface. Immediately after WdfUsbTargetDeviceCreate if I break into the debugger and examine the newly created device, then I see that the Control Pipe Handle is NULL! Here is the actual output: -------- kd> !WDFUSBDEVICE 0x0000057f`fe5905f8 WDFUSBDEVICE 0000057ffe5905f8 ============================= Config descriptor fffffa80037216b0, device descriptor fffffa8001a6fb58 Control USBD_PIPE_HANDLE 000000000000...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

How do I print only odd pages in Publisher and then only even page
I am attempting duplex printing with a printer that is unable to do duplex printing by printing odd pages first and then flipping the pages over and printing only even pages. No option is given that I can find in the print window. Help! Sarah Wright wrote: > I am attempting duplex printing with a printer that is unable to do > duplex printing by printing odd pages first and then flipping the > pages over and printing only even pages. > > No option is given that I can find in the print window. > > Help! The duplex printing add-in can be found here (http://publisherm...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

Cannot create mailbox for new user accounts
I am running Exchange 2003 on Server 2003. I was able to move all existing mailboxes from my old Exchange 2000 server to my new 2003 server. Whenever I try to create a new user it creates the user normally and asks if I want to create a mailbox for the user. I check yes, and it says account created successfully. However, the mailbox never appears in Exchange, no email address is assigned to the new user, and I cannot send mail to the user account either. If I try to access any Exchange properties from ADUC it shows everyhting as if a mailbox exists, but there is no mailbox. I am ...