Add Columns with specific text in a row

**TheTank67** · 03-21-2013, 11:09 AM

I am trying to track street pavement for a city and as you can imagine, there is a lot of data entry.

At the end of the day, I will need to know the total length of streets with brick, asphalt, and concrete. Column 'D' has the street material (Asphalt, Brick, Concrete) and Column 'E' will have the length. At the bottom of the sheet, under column 'E', I have cells that will show the total length for streets with Asphalt (E61), Brick (E63), and Concrete(E65), so I need a formula to enter in those cells that will look for the specific pavement materials it is totalling

Also, I am tracking streets with/without curb and gutter. If a street doesn't have it, I need to track how much will be needed. Basically, it will be the length (Column E) times 2 (both sides of the street). In column 'G' there will either be 'Yes' or 'No'. If 'Yes', then I don't need a total and the cell containg the amount of curb and gutter need (Column 'J') will be blank. However, if 'No', Column 'J' will have the total amount of curb and gutter needed ('E??' x 2)

Obviously the sheets will be different lengths so the cells will need to be copied and pasted.

Thanks for your help.

**Pepe Le Mokko** · 03-21-2013, 03:42 PM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**FDibbins** · 03-21-2013, 05:11 PM

Hi

for part 1, try using sumif()

=sumif(D:D,"Asphalt",E:E) you can either hard-code Asphalt and the others, or reference them in a small table

For the 2nd part, you can use the same basic formula, but change it to sumifS(), which will allow you to add extra criteria

**TheTank67** · 03-22-2013, 01:24 PM

Thanks to both of you for responding.

After posting, I found an answer yesterday and used:

=SUM(IF(D7:D101="ASPHALT",E7:E101))

and then for the curb and gutter I used:
=IF(G19="NO",J19*Lookup!$B$7,0)

Thanks again

**Pepe Le Mokko** · 03-22-2013, 01:56 PM

BTW, it is bad practice to use a reserved name for a sheet name . As LOOKUP is an existing function, it would be best to call your sheet something else