Creating a formula that queries data in Column B based on a selection from Column A in Microsoft Excel

Question

I am reasonably familiar with Excel formulas but I need to construct a more complex query and am not having much success at the moment.

I have simplified what I am trying to achieve for the purpose of the question.

Here is my source data:

What I am trying to do is construct a formula which will summarise the 'fed' status of each AnimalType. Here is a long hand version of what I am trying to do:

Using the range A1:A7, IF values in Column B = "Fed" WHERE AnimalType = "Cat" display 'All Animals Fed' ELSE display "All Animals Not Fed". Therefore, the output I would see for this table (using the conditional formatting rule) would be:

I hope this example provides enough detail for what I am trying to do. The actual solution I need is slightly more complicated than this but if I figure out how to to the above I am fairly confident I can figure out the rest.

Answer 1

Select B1:B2 cells in your "Recap" sheet, and type this formula:

=IF(ISERROR(MATCH($A$1:$A$2&"Not Fed", Animals!A$2:A$7&Animals!B$2:B$7, 0)), "All Animals Fed", "All Animals Not Fed")

This is an array formula, so you need to "validate" it using Ctrl+Shift+Enter.

In order to work, you need to replace "Cats" with "Cat" and "Dogs" with "Dog", or you can adapt the formula to delete last character.

Answer 2

I would create a Pivot Table (Insert ribbon) from the range. I would drag the AnimalType field to the Row Labels zone, and the Fed? Field to the Column Labels zone. I would drag the AnimalType field to the Values zone.

This will give you a count of Animals by type and Fed status, with row and column totals. These can answer all the questions you are trying to answer, without coding any formulas, in a structure that can easily be extended to include other columns.