I have a spreadsheet of orders of fruits. The varieties of fruits contained in a given order are all written in the same cell, coma separated. If I want to get the number of orders that include bananas, I do the following
=COUNTIFS('Order List'!C:C,F$1,'Order List'!AG:AG,"*Banana*")
If I want to get the number of orders that include oranges, I do the following
=COUNTIFS('Order List'!C:C,F$1,'Order List'!AG:AG,"*Orange*")
This works.
Now I want to know how many orders include bananas and/or oranges. The sum of the 2 previous results does not work, since any order including both Oranges and Bananas would be counted twice.
I could not find a way to write the regular expression that matches the case where both appears (to subtract the intersection), because 1) there is no ordering in the list, so banana could appear before or after orange and 2) because the number of intersections grows too fast when more items are in the list.
It seems a simple problem but I haven't figured out how to do this, your help is very much appreciated !
