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;
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.