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.