# How to use an array as a condition in a COUNTIFS or SUMIFS formula

This question could be tricky to follow so I'll split it out with examples

Given I have this table of overpriced fruits;

``````Item          Price
Apple         £0.80
Peach         £1
Pear          £1
Apple         £1.20
Banana        £1
Orange        £1
Orange        £1
Apple         £4
``````

I then have another table using the COUNTIFS and SUMIFS formula;

``````My Item       Count     Total Price
Apple         3         £6
Peach         1         £1
Pear          1         £1
Banana        1         £1
Orange        2         £2
``````

The formula used in the second table are;

• Count: `COUNTIFS(Item,"*" & A2 & "*")`
• Total Price: `ABS(SUMIFS(FirstTable::Price,Item,"*" & A2 & "*"))`

where "Item" references the first table (A2:A9), "Price" (B2:B9) and "A2" references that particular row in the second table (A2:A5).

As you can see, the `Count` simply counts up the number of times "Apple" appears in the table. In my use (not delicious fruits), I do a wildcard search before and after the string, so in this example "Pineapple" would be counted in with "Apple". The `Total Price` calculates the total "Price" of fruits where the "Item" name contains the string in A2 (again with wildcard).

However, the first table is no longer a list of fruits, imagine it is thousands of items bought from a shop. So amongst all the fruit names are vegetables, meat and other foods.

Is there a way to use an array where we have previously used "A2"?

So now we have a table called `config` which is as follows;

``````Fruits        Veg           Meat
Apple         Pepper        Pork
Peach         Carrot        Beef
Pear          Onion         Chicken
Orange        Mushroom      Fish
``````

So now our new table, rather than summing up each individual fruit will be;

``````My Item       Count     Total Price
Fruit         7         £10
Veg           0         £0
Meat          0         £0
``````

So the formula for `COUNTIFS` would be something like (in pseudo) `COUNTIFS(Item,"*" &` array of config::Fruits `& "*")`.

I am aware that you could list each test-values and conditions, but that is not very maintainable when new items are added. Additionally having separate tables for fruits, veg and meat is not a perfect solution either because I would also like to add a column for 'other', where it can sum up all items which have not already been accounted for in either of fruits, veg or meats.

So basically I wish that a user can add "Banana" to the `config` table (which was purposefully left out), and the summary table will update fruits to show a count of 8 and a Total Price of £11...without having to amend any of the formula. Hopefully that all makes sense and I appreciate any comments or help - I am not even sure there is an ideal solution.