Multiple AND/ OR criteria and summation

Excuse my ignorance..

Essentially in column A I have a list of products; for argument sakes vegetables. (Note there are a few thousand unique entries)
In column B I have a list of the amount of stock we have left of each of the product.

What I need is to order the products in a set of defined subgroups. Using vegetables as an example I would want to put them all into sub groups based on colour. I know what subgroup each vegetable will go in, but it’s a pain to go through them all, as you may well imagine, so I’m trying to do it by determining a set of rules.

In column C, I used the equation ‘=LEFT(A:A,5)’ to get the first 5 characters. And based on this can do a SUMIF(C:C, “(a 5 character code I define)”, B:B) and that works fine.

In column D I use the ‘=RIGHT(A:A,3)’ to get the last 3 characters. What I am looking to sum in one of the subgroups is where the last 3 characters is “IIL” (for example) AND where in column C they have a range of 5 character codes that I want to sum.

So I’m looking to sum everything in column D with last 3 characters “IIL” which also has column C of “XXXX1” or “XXXX2”.
I hope the spreadsheet I attach clears up any doubts in my questions. Thank you in advance.

LJ.

---------- Post added at 02:31 PM ---------- Previous post was at 02:30 PM ----------

Can not actually upload at the moment. So i will try my best to make it as easy as I can for you to help me.

Try:

=SUM(COUNTIFS(D:D,"*IIL",C:C,{"XXXX1","XXXX2"}))

To sum column B change to:

=SUM(SUMIFS(B:B,D:D,"*IIL",C:C,{"XXXX1","XXXX2"}))

Are you using Excel or OO or GoogleDocs? In Excel, the { entries should work....

Try copying my formula and pasting it. Does it accept it?