Count function with multiple qualifying criteria

It is always better to include a sample workbook so people do not have to duplicate your work. Also, somewhere, you have to tell the application what fruit is. This will probably involve the find command and an array formula, but I'll wait to see the data.

Attach a sample workbook (not a picture or pasted copy). 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.

Here is one way to do it, but I am not satisfied. Although you can have as many people as you want and they can eat anything they want, there is not much flexibility if you add a new fruit to the menu. You would have to add another column with a specific formula. I'll post it for now, but I will continue to work on a more general solution: on that gives the answer by referencing the orange (in color) table. I would like to do this with an array formula. If can be done relatively easily in VBA.

Your best for doing this is creating a table to establish what food item is what category of food. Then in your input, having each item in its own column for a given person instead of the same cell. That would make it pretty easy to do, easy to add additional food items/categories, and deal with higher numbers of food items per person than is reasonable.

I gave up on the array formula for a while. Here is a VBA implementation. With this function, you can add people, they can eat whatever they want and you get to decide what fruit is on the orange table.

I converted your data into a table: Table_Lunch and I put the fruit in another table: Table_Fruit. These names are used in the code. You do not need the helper columns with this code.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

Please Login or Register  to view this content.

I got it using formulas only (with tables and named ranges for ease), should also be very easy to add new fruits and categories. I also provided a results sheet with drop downs to get the results of any food item and any category you want.

See attached.

I have 4 new sheets. Lists is just the list of categories, simply add new items directly under last and the list will grow. The drop downs for category in the file are based on this list.

The Key sheet is a table that relates food item to category, IE how Excel will know an apple is a fruit. Simply enter a new food item directly under the last and use the drop down for category to select its category.

The input sheet is where you place a person and their selections of food item. I have provided 7 columns, it should be very feasible to use more or less. At the right side of this input table are the magic formulas. The 1 is simply a count of the food items on that row matching the desired item to lookup from the Results sheet B2 (as saved "apples"). The next column counts how many items on that row are of the desired category selected on Results D2, using the Key table to determine what food items match what category.

The results sheet provides a count by # of people who had the food item or the category of food and also a total count of that food item and category.

Let me know if you have questions