MS Excel Formula?

Question

I have a worksheet for calculating building materials. I have in column "c" the type of material (2x4,2x6...). In colum "d" I have the quantity.

Is there a formula to seach the whole "c" colum to find the "2x4s" and add the quantities from the adjacent colum "d" and give me a total for only the "2x4s". Then I can write another one for the "2x6s". I want to do this WITHOUT sorting.

This way I can have a running total of the materials as I add to and change them.

WARNING!! All Abusers (non-answers) will be Reported.

Answer 1

Yes! Use the sumif formula



you write sumif(c2:c100,"2x4",d2:d100). This looks all thru column c but adds only the quantities in column d using the description given in the formuala's center



or you can use cell references for "2x4" where you have that exact description



Good luck

Answer 2

With my very basic Excel knowledge, I think you can make in column E a counter column.



E1: =IF(C1="2x4",D1,0)



Then, you can sum that column for the total 2x4s. Repeat for your other products. It's definitely not the best way, but I'm pretty sure it works.



Yeah, annitot, has a better answer using sumif.

Answer 3

A pivot table would be the simplest, but you want a formula so OK. First the pivot table. Highlight all of the cells that contain data, including the header information. Go to Data>Pivot Table. When the dialog box opens, Click Finish. The Pivot Table Field list will open. Drag the data to the far right column and to the data field. This will be automatically set to count. It will give you a count and a summary of each down the left column. Right click on the top left gray button and choose Field Settings. A dialog will open allowing you to adjust for your needs. Anytime you add new data, you will need to refresh the pivot table by right clicking anywhere on the table and select Refresh. AutoFilter: Select the cell at the top of your list and click Data>Filter>Auto Filter. A drop down box will appear. Click on the arrow and select the filter criteria "New Dehli", no quotes. Look at the bottom left corner of your screen. It will say "x of xx records found". This will give you a quick count. Formula: Select and empty cell and type =countif(a1:a1000,"New Dehli") This will count all of the records that have the exact text "New Dehli", no quotes, in them. You will need to select several cells to place this formula in. Replace "New Dehli" with "Agra", etc. as needed. This can be done on your summary tab. It does not have to be on the same work sheet.

Answer 4

If you have a list of building materials and the goal is to summarize the list to place an order, I would use a pivot table. It will provide you with all the items, summarize quantities and list price. I have used them extensively and they work great.

Answer 5

in a cell of E column,

=SUMIF(C:C,"2x4s",D:D)

this will add all quantities in D column if corresponding rows in C column have 2x4s in it.

Answer 6

Try this web site. EXCELlent!

http://www.mvps.org/dmcritchie/excel/excel.htm

Answer 7

you could use the 'If' statement in Excel. Look it up in Excel help (F1).

Answer 8

you should post this to mrexcel dot com. they have real experts in excel and it won't waste your time!