SUMIF criteria: anything but Apples?

Hi all, I'm trying to set up a formula, but I don't know how to do the SUMIF criteria for it.

I have column A with fruit names and column B with prices, and I need to find out the difference between the sum of apple prices (all apples (incl. different types)) and the sum of all other fruits prices. Which sounds WAY more complicated if I put it in these words the formula is simply:

SUM (ALL APPLE PRICES) - SUM (ALL NON APPLE PRICES)

for which I am trying to use SUMIF as:

=SUMIF([Fruits],"Apple*",[Prices])-SUMIF([Fruits],NOT("Apple*"),[Prices])

but it doesn't seem to work.. when I use evaluate formula, I can see that NOT("Apple*") gives value error.. which I think I understand why...

but I don't know how else to set the SUMIF criteria up to mean SUM anything and everything that is not "Apple*"

Can I get some help with this please?

Thank you!

Try:

Formula:

Please Login or Register  to view this content.

Always more rewarding when you work it out for yourself