Please help with food inventory calculations!

[ahh]

I am working with a food inventory spreadsheet, and I need help coming up a function involving vlookup. I have a column entitled "food category" where each food is listed as "fruit", "vegetable",etc. then I have a column for the vendor price and a column for the units of food consumed per month. I want to calculate the cost of each "food category" per month. So, for instance, to calculate the "fruit" cost, I want excel to look in the food category column and check which items are fruits. For those that are, I want excel to multiply each fruit item's price times the amount consumed and then sum each fruit's cost. Can someone please help with the correct function?

Thanks in advance!

 

[GK039]

Assuming the following:

Data ranging from row 1 to 25 for the example

Col A Month
Col B Food Category
Col C Price
Col D Units Consumed


Cell G2 Data Validation with months
Cell H2 Data Validation with Food Category

Cell G4:

=SUMPRODUCT(--(A2:A25=G2),--(B2:B25=H2),(C2:C25*D2:D25))

it will give you the total based on the criteria you will place in the data validation cells

George

I am working with a food inventory spreadsheet, and I need help coming up a function involving vlookup. I have a column entitled "food category" where each food is listed as "fruit", "vegetable",etc. then I have a column for the vendor price and a column for the units of food consumed per month. I want to calculate the cost of each "food category" per month. So, for instance, to calculate the "fruit" cost, I want excel to look in the food category column and check which items are fruits. For those that are, I want excel to multiply each fruit item's price times the amount consumed and then sum each fruit's cost. Can someone please help with the correct function?

Thanks in advance!