Excel many to many

[cdiogo]

How can I in Excel, resolve this question about this table:


Invoice Number | Item | Product | Value
1001 | 1 | potatoes | 100
1001 | 2 | tomatoes | 150
1002 | 1 | potatoes | 100
1003 | 1 | water | 50
1004 | 1 | potatoes | 100
1004 | 2 | onions | 120


If I ask the questions:


Which an how many of these invoices have no potatoes or tomatoes ?
the answer should be, in this case: invoice 1003 / number = 1


How can I find it in Excel? Is there a formula for this question?

 

[SpillerBD]

I can post a sample linked file when I get home this evening.
What version of Excel are you using? 2016 or earlier?

 

[SpillerBD]

See example at http://1drv.ms/1izecNC

 

[g3cw]

I would put this formula in E2 and copy it down thru all the rows:
=IF(AND(A2<>A1,C2<>"potatoes",C2<>"tomatoes"),1,0)
It returns a 1 in row 5 for invoice 1003; and 0 for all other rows in the sample data set.
it does depend on having the data sorted by invoice number.