Excel - Sum if multiple values that meet criteria from two tables

[7527amanda]

Have tried numerous options to accomplish the following task without success

- lookup a recipe in a column in the recipebook and sum all the non blank cells in that row if the corresponding individual column header (ingredients) meets certain criteria (halal, from australia etc)?

i have tried this previously and cannot get it to work

IF(SUM(MMULT(IF(ingredientslist=IF(OFFSET(ingredientsrow,MATCH(recipe,recipelist,0),0)<>"",ingredientsrow),1,0),ROW(ingredientslist)^0)*IF(origincolumn="Australia",1,0))=COUNTA(OFFSET(ingredientsrow,MATCH(thisrecipe,recipelist,0),0)),"Yes","No")

Would appreciate any suggestions.

Thank you

[shukla.ankur281190]

You attached file XL doesn't seems the same file which pic you have uploaded? Do upload a sample file. Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[7527amanda]

Hello,

thank you for the reply

I have a specification sheet where I need to say a product claim i.e. halal, australian. The information is drawn from two other workbooks but have placed them together in the attached.

I need to look up the recipe in the "recipesheet" and sum the ingredient values of each ingredient(shown in column header of recipesheet)if it satisfies a criteria. Criteria i.e. austtralian by looking up the ingredient in ingredientmatrix.

This formula goes into a specification sheet i.e. %|australian ingredients or yes|halal etc

Does that make sense?

[JohnTopley]

Please sample expected results for your file.

[7527amanda]

Hi,

I have updated the attached file with a semi blank specification sheet to which the final values will go (note that most values are blank as the links are missing).

I need may two formulas as some will require further calculations such as sumif and some will just require a "suitable" or "not-suitable" result:

- to look up each in the receipesheet i.e. nbc and sum all of the ingredients i.e. r0005 in that recipe row if all the ingredients in that recipe according to the ingredientmatrix meet the criteria i.e. australian
- to look up each in the receipesheet i.e. nbc and if all of the ingredients i.e. r0005 in that recipe row according to the ingredientmatrix meet the criteria i.e. halal, vegan. then provide an answer.

thanks

[AliGW]

The product information sheet is full of #REF! and #N/A errors as it is referring to another workbook. Please attach again with these values pasterd in as values, not formulae.

[7527amanda]

Hi,

thanks for looking

I was reminded to remove all proprietary information and as this is a specification sheet that is what is contains. The spreadsheet is actually too large and it is linked to 3 other workbooks and contains multiple sheets containing proprietary information. I cannot move any of workbooks without the data being dereferenced.

All other information and formula are working as they should, the only two I can't work out are as described below with the expected results highlighted in the specification sheet.

Could you advise what other information you were looking for?

[AliGW]

The contents of cell A2 would be useful.

[7527amanda]

A1 os the finished product code and a2 is the description. The formulas just draw this information from a specification table.

[AliGW]

So what should/could they be? Please provide values. Remember that all of this is obvious to you, but we are just seeing error messages.

[7527amanda]

I have updated the attached file but please understand that having all the other irrelevant information available will make everything confusing to another user not familiar with the company and the requirements. The specification sheet is quite complicated and is linked to and drawing information from 10 worksheets in 3 workbooks.

The only two formulas that will go into the specifcation sheet that I require the ones that need to look up the recipesheet and ingredientmatrix and return values.

Hope this helps