Summarize Cable Schedule Easily with Google Sheet


This tutorial will show how to summarize cable size & length, cable gland, and cable lug from cable schedule easily with Google Sheet.

Steps:

  1. Copy Cable Size & Cable Length column from cable schedule spreadsheet to Google Sheet. Rename this sheet as (for example) Cable. Example as follow (range data B3:C21).

  2. To summarize cable size with its length, go to another cell (example E2), add the following formula:

    =QUERY(B3:C21,”select B, sum(C) group by B”)

    It will look like Pivot Table without the complexity.

  3. To summarize cable core & lug, go to another cell (example H2), add the following formula:

    =QUERY(B3:B21,”select B, count(B) group by B”)

    This will count how many pulling cable based on identical cable type & size. The formula will take effect on column H & I.

    To count cable lug, multiply cable pulling count with cable core quantity times 2 (source & destination). Add the following formula on column J (for this example from J3 through J8):

    =$I3*(LEFT(H3,1)*2)

  4. Create a new sheet, rename it to (for example) Gland. Copy Gland Size & Gland Type column from cable schedule spreadsheet to Google Sheet. Example as follow (range data B3:C40).

  5. To summarize cable gland, go to another cell (example E2), add the following formula:

    =QUERY(B3:C40,”select B, count(B) group by B pivot C”)

    It will look like Pivot Table without the complexity.

R. Bambang Widiatmoko

I'm an engineer and working in engineering consultant office. I have some experience in computer application in graphic design (CorelDRAW, Inkscape, GIMP), AutoCAD, and Office. I also like to read technology news.

You may also like...