Formula to SUM for a Given Field Type Specified in another Column

[R_S_6]

Hi All,

I'm sure this will be a very simple one for someone - it leaves me *head scratching* though!
I have attached a VERY generic example and it includes what I am trying to SUM for the given data.
From the example if we look at Question Z1:
"Total Cost of All types of "Lemons" " = ??
I could simply do

Please Login or Register  to view this content.

, however this is a very simplistic spreadsheet for me to learn the formulae to apply to my spreadsheet that is MUCH bigger.

I require the formula to:
Sum the costs of all the Types of the Fruit, when the fruit is specified by "H4". I.e. if Column A if = to "Lemons" as specified by "H4", SUM the Relevant section of Column D.

To move on further, e.g Question Z4, Sum the cost of all the Fruits as specified by "H4" that their 'Use' is equal to "I4", i.e. 'Lemons' to 'Eat'.

I guess the above may require some IF statements and or VLOOKUP, I'm just not at all sure how to setup the formula to get the required outcome. Any help would be appreciated - Sorry for the long winded explanation!

Cheers,
Mark

[oldchippy]

Hi Mark,

You can use SUMPRODUCT for this one

[R_S_6]

Oldchippy - Thanks for the reply. That seems a handy function!

I feel I may have described the issue a little wrongly in the original post -

I require formula to refer to a cell, in this case "H4" which is 'Lemons'. I then require the formula to search for 'Lemons' in Column A (it occupies rows 2-9), then sum the cost of all the types of Lemon (A-H). That would answer Question Z1.

For Question Z8 for example.
I require formula to refer to a cell, in this case "H5" which is 'Apples'. I then require the formula to search for 'Apples' in Column A (it occupies rows 10-17), then sum the cost of all the Apples that are Required as specified by Cell "J4", in this case 'Yes'. This would then provide the total cost of 'Apples' that are Required.

I hope the above makes things a bit clearer...?
I use the values in columns H-J to help specify the formula criteria; as to go through and highlight each block of 'Quantities for Lemons' the 'Cost of Lemons' for example would take a long time on my actual spreadsheet.

Cheers

[oldchippy]

Hi,

To do that you would need to reformat column A so that the type of fruit is in each row so for instance "Lemon" would appear in each cell A2:A9 then you can use this formula

=SUMPRODUCT(--(A2:A9=H4),C2:C9,D2:D9)

Adjust the others to the same format and you should be able to work it out

[R_S_6]

Thats great - I'll give that a go now! Thanks for your prompt and very useful replies!
Cheers,
Mark

[R_S_6]

Works a Treat! Thanks Again!