Filter using two queries

Question

I have a spreadsheet with two tables --- Master Table and Table A

Each table has the same number of columns (8) and rows (8). Master Table range is A1:H8 and Table A range is A10:H17. In both tables, column A indicates "size" and column B indicates "color".

The Master Table has items with all possible sizes and colors, whereas Table A only has items with size "Large" and color "Blue" which appear in the Master Table

I would like the link the two tables.

I am using the Filter feature to do this. In Table A (cell A10), I am using the formula:

Filter (A1:H8, A1:A8="Large"). This gives me all items in Table A that have "Large" in column A of the Master Table. This works fine.

I would like to reduce the items in Table A that have both Large (in column A) and Blue (in column B) of the Master Table.

What would I need to add to the Filter feature to accomplish this?

Thank you

Answer 1

To filter on multiple criteria, multiply the criteria expressions. Since each expression is boolean, it evaluates to 0 or 1. The result of the multiplication will be 1 only if all the expressions are true.

Filter (A1:H8, (A1:A8="Large")*(B1:B8="Blue"))

See https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759 for more info on the FILTER function.

Answer 2

If this formula is on one sheet (i.e Sheet 1) and the reference data is on Sheet 2, would this be the correct formula: =Sheet2!SUMIF(A1:A8,"LARGE",B1:B8)

Answer 3

To prevent the FILTER from showing #CALC when no rows satisfy the criteria, imbed the FILTER function inside an IFERROR function

=IFERROR(FILTER(A1:H8,(A1:A8="Large")*(B1:B8="Blue"))," ")

This will mask more condition than just #CALC so be careful with your data.

Answer 4

It would not be a filter. The formula you want is

=SUMIF(A1:A8,"LARGE",B1:B8)

Answer 5

If in my previous example, column B had "numbers" and not colors.

I then want the "sum" of those items that had a size "Large" in column A.

What would the Filter feature look like?

Thank you