sum formula with multiple criteria

tang423

New Member
Joined
Jul 6, 2012
Messages
3
Hi

I hope that someone can help me please.

I have set up a food spreadsheet where there are a number of entries that fall under the food item e.g Garlic Puree (see copy of spreadsheet below) and I need to calculate on a separate worksheet the total amounts required (Column G) if it meets the criteria Garlic Puree (column A) and 1st Shop (Column K).
I hope someone can help me with writing this formula please as I don't know where to start.

With many thanks

U
FoodDate and MealQuantity per personNo. of peopleQuantity per packweight/volumeQuantity requiredNo. requiredNotesGrocery SectionShopping
PastaFriday 3 August 2012 Dinner70261000gram18201.8Rice and Pasta1st Shop
onionFriday 3 August 2012 Dinner0.16261001gram4.160.0Fresh fruit and veg1st Shop
Garlic pureeFriday 3 August 2012 Dinner4.4375261000gram115.3750.1Herbs and seasonings2nd Shop
Dolmio pasta sauceFriday 3 August 2012 Dinner12526500gram32506.5Sauces1st Shop
Vegetable OilFriday 3 August 2012 Dinner15.0261000ml3900.4Vegetable Oil1st Shop
CarrotFriday 3 August 2012 Dinner30261000gram7800.8Fresh fruit and veg1st Shop
Vegetable OilFriday 3 August 2012 Dinner15.001000ml00.0Vegetable Oil1st Shop
onionFriday 3 August 2012 Dinner0.200100.0Fresh fruit and veg1st Shop
CarrotFriday 3 August 2012 Dinner3001000gram00.0Fresh fruit and veg2nd Shop
Lettuce - icebergFriday 3 August 2012 Dinner0.02261head0.520.5Fresh fruit and veg1st Shop
Lettuce - icebergFriday 3 August 2012 Dinner0.02261head0.520.5Fresh fruit and veg2nd Shop
Lettuce - icebergFriday 3 August 2012 Dinner0.02261head0.520.5Fresh fruit and veg2nd Shop
Lettuce - icebergFriday 3 August 2012 Dinner0.02261head0.520.5Fresh fruit and veg1st Shop
Cherry tomatoesFriday 3 August 2012 Dinner1826500gram4680.9Fresh fruit and veg2nd Shop
Cherry tomatoesFriday 3 August 2012 Dinner1826500gram4680.9Fresh fruit and veg1st Shop
CucumberFriday 3 August 2012 Dinner0.04261per cucumber1.041.0Fresh fruit and veg1st Shop
Cheddar CheeseFriday 3 August 2012 Dinner2526500gram6501.3Dairy1st Shop
Vegetable OilSaturday 4 August 2012 Dinner15261000mlVegetable Oil1st Shop
Chicken breastSaturday 4 August 2012 Dinner10026500gram26005.2Meat1st Shop
Garlic pureeSaturday 4 August 2012 Dinner4.3261000gram111.80.1Herbs and seasonings1st Shop
onionSaturday 4 August 2012 Dinner0.252616.56.5Fresh fruit and veg1st Shop
milkSaturday 4 August 2012 Dinner0.2261pint5.25.2Dairy1st Shop
Vegetable OilSun 5 August 2012 Dinner26.62501000ml00.0Vegetable Oil1st Shop
OnionSun 5 August 2012 Dinner0.501100.0small onionFresh fruit and veg1st Shop
Garlic PureeSun 5 August 2012 Dinner301000gram00.01 tsp per 2 peopleHerbs and seasonings2nd Shop
EggSun 5 August 2012 Dinner0.501200.0Eggs1st Shop
MilkSun 5 August 2012 Dinner18261000ml4680.5Dairy1st Shop
butter/magarineSun 5 August 2012 Dinner12.5261000gram3250.3Dairy1st Shop
PotatoesSun 5 August 2012 Dinner225261000gram58505.9Fresh fruit and veg1st Shop
PeasSun 5 August 2012 Dinner10001000gram00.0Frozen1st Shop
Vegetable OilMon 6 August 2012 Dinner15271000ml4050.4Vegetable Oil1st Shop
onionMon 6 August 2012 Dinner0.250100.0Fresh fruit and veg1st Shop
Garlic PureeMon 6 August 2012 Dinner8.601000gram00.0Herbs and seasonings1st Shop
Vegetable OilMon 6 August 2012 Dinner0.501tbsp00.0Vegetable Oil1st Shop
milkMon 6 August 2012 Dinner0.1901pint00.0Dairy2nd Shop
SweetcornMon 6 August 2012 Dinner28.7501000gram00.0Frozen1st Shop
Cheddar CheeseMon 6 August 2012 Dinner200500gram00.0Dairy1st Shop
CrispsMon 6 August 2012 Dinner6.25025bag00.0Crisps1st Shop
pastaMon 6 August 2012 Dinner56.2501000gram00.0Rice and Pasta1st Shop

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,723
Office Version
  1. 365
Platform
  1. Windows
Change sheet and cell reference to where your data is.
This formula assumed data started in row 2.


=SUMPRODUCT(--(Sheet2!$A$2:$A$40="Garlic Puree"),--(Sheet2!$K$2:$K$40="1st Shop"),Sheet2!$G$2:$G$40)

<COLGROUP><COL style="WIDTH: 543pt; mso-width-source: userset; mso-width-alt: 26477" width=724><TBODY>
</TBODY>

Also you may want to look at the attched video on SUMPRODUCT.

http://www.youtube.com/watch?v=vxpeEf8MYaY&feature=plcp
 

tang423

New Member
Joined
Jul 6, 2012
Messages
3
Thank you so much for your help... the formula worked perfectly.

thank you once again

Change sheet and cell reference to where your data is.
This formula assumed data started in row 2.


=SUMPRODUCT(--(Sheet2!$A$2:$A$40="Garlic Puree"),--(Sheet2!$K$2:$K$40="1st Shop"),Sheet2!$G$2:$G$40)

<tbody>
</tbody>

Also you may want to look at the attched video on SUMPRODUCT.

http://www.youtube.com/watch?v=vxpeEf8MYaY&feature=plcp
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,723
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top