sum formula with multiple criteria

T

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>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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
 
Upvote 0
Thank you so much for your help... the formula worked perfectly.

thank you once again

AhoyNC said:
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
Click to expand...
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
775
Fluff
Fluff
1
  • Question
Sum total filtered data only, with multiple criteria
Replies
1
Views
301
1988craig
1
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
482
fjns
F
S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
762
dmt32
D
A
  • Solved
HLOOKUP returns #N/A, but the value is there
2
Replies
10
Views
1K
Anthony47
A

Forum statistics

Threads
1,214,932
Messages
6,122,334
Members
449,077
Latest member
Jocksteriom

Share this page

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
Back
Top