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.

You must log in to answer this question.

Browse other questions tagged .