Help with Excel Formula

[Renner07]

So i work for a construction company and i have a spreadsheet i use to help me with doing quantity takeoff, so basically looking at the blueprints and figuring out how much it will cost to build it. I have a spreadsheet i use for doing finishes in each room such as ceramic tiles on the floor, paint on the walls and acoustic tile on the ceiling for example. I want to know how i can set up a formula so i have a summary at the top of my page that gives me a total amount for the complete job how much much there is for each finish. do i use a lookup formula or a if statement i'm not sure where to start. I did figure out how to do it for one cell but i need it for about 200 cells in a column. I want the formula to look down the complete column find where it says the words ceramic tile and from that add up the corresponding area(m2) and give me a complete area for ceramic tile. Hope that makes sense.

Thanks,

 

[jeffreybrown]

Hello & Welcome to the Board,

You might try Sumif...

=SUMIF(A1:A200,"Ceramic Tile",B1:B200)

 

[Renner07]

Thanks Jeff that worked perfectly. Awesome with the fast reply to.

 

[jeffreybrown]

You're welcome and thanks for the follow-up

 

[Renner07]

i will make sure to use this site from now on for excel help

 

[Renner07]

Actually i have another question. Its for the same worksheet. At the top of my spreadsheet i have a table with 2 columns. 1 column is abbreviations and the 2ND column is the abbreviation written out in full word. Exp. "CT" - "Ceramic Tile"

I have already figured out the answer to my question my my answer is a ridiculous long formula. I wanted to know if i could shorten it up with one general formula.

So i want to know how to make a cell automatically type in "Ceramic Tile" when i type in the abbreviation "CT" so i can enter everything quicker rather then typing out the whole word. Right now i have like 40 if statements within if statements it make it work. Also i could only figure it out by having a abbreviation cell beside my actually answer. So i had to type in CT and have a separate cell beside it where it writes out Ceramic Tile. Is it possibly to write CT in the same cell where ceramic tile will write out. There is 4 columns i need this to work for but i guess if i make it work for 1 column i could just copy and paste it. There is about 200 cells within the columns.

Hope this makes sense again.

 

[jeffreybrown]

A Lookup function would work great for this situation. Create a table of all your values...

Then use =LOOKUP(A1,E2:F5) where A1 holds the value such as CT and the formula is in B1.

The table is E2:F5 where Column E has the abbreviations and Column F has the names.

 

[jeffreybrown]

Dup

 

[Renner07]

Going to try it right now, let you know if it works

 

[Renner07]

I seem to be having some trouble with it. I was going to send you a screen shot that explained with arrows what i was looking for but i didn't know you cannot attach files to reply's. Is there a generic email you have i can send this picture to to show you what i mean?