how to total based upon criteria

**mkmed** · 07-16-2008, 10:03 AM

Hello all,

I have created a workbook to calculate material usage of wood. I am trying to streamline the data entry and automate a segment and am having difficulty. I am using excel 2007 but need to be compatible with 2003. Row two in every other column has a species of wood from list (oak, ash, cherry etc...) The 10th row below this one is a board footage tally for this item. What I need the formula to do is look at the cell with the species and tally all like species together.

Example:B2=Hickory and C11 is 30
D2=Hickory and e11 is 80
F2= Oak and g11 is 212
H2=Hickory and i11 is 64

What function can I use to tally all like species together to subtotal each different species quantity?

Thanks for any help!!

Mark

**oldchippy** · 07-16-2008, 10:10 AM

Hi,

Something like this,

=SUMPRODUCT(--(B2:H2="Hickory")*--C11:I11) subtitute the different species

=SUMPRODUCT(--(B2:H2="Oak")*--C11:I11)

**NBVC** · 07-16-2008, 10:12 AM

You can use a pivot table...

or you can list your species and then use a Sumif() formula...

e.g. =Sumif($B$2:$H$2,"Hickory",$B$11:$H$11)

you could replace "Hickory" with the cell reference in the header that contains the word, so that you may copy the formula across the summary sheet.

**mkmed** · 07-16-2008, 10:22 AM

Thanks guys those do work, but my post wasn't clear. I do need the totals to sum as your formulas work, but I also need the species from row two to be copied down. My example only shows two species, but the sheet will allow many different in an order and the list (named range from "data" sheet) could literally be hundreds of different species. Is there a way to use one of the lookup formulas? Thanks for any help!!

In other words, I need a cell (in this case K13) to return the specie name and the adjacent cell (L13) to return the qty for specie A and k14 and L14 to return the qty and so on...

Thanks again.

Mark

**NBVC** · 07-16-2008, 11:38 AM

Use a Pivot table...

http://www.cpearson.com/excel/pivots.htm

http://peltiertech.com/Excel/Pivots/pivottables.htm

**oldchippy** · 07-16-2008, 11:39 AM

Hi,

Something like this using Data Validation?

http://www.contextures.com/xlDataVal01.html

**mkmed** · 07-16-2008, 12:09 PM

You guys are AWESOME!! This works great, but is there a way to automate the process? My wood species uses data validation in row two (user input) and row 11 calculates qty. I would like my data in row 13, 14, 15 and 16 to be automated if possible and "look at row 2" and see which columns have duplicate entries and tally all that duplicate together. Is this possible?

Thanks again,

Mark

**oldchippy** · 07-16-2008, 03:23 PM

Hi Mark,

The best thing is is to post a sample of your sheet with the result you want.

I'm away for a few days now, but back after the week-end. If no one picks it up I'll take a look on my return.

**mkmed** · 07-17-2008, 06:26 PM

Please see the attached file. I want the options in K13, K14 and K15 to fill in automatically depending upon the specie in row 2.

Thanks for any help

Mark