questions about sumif

I have some trouble using sum and sumifs, I have data similar to below,

items Name Color free cost
item 1 red, blue, yellow Y 100
Item 2 blue, yellow,black, white Y 50
Item 3 red, yellow N 20
Item 4 green, N 12
item 5 green, red, blue, Y 22
item 6 White, red Y 24

Now I wanted to find "color" contains "blue or yellow or white", AND free = Y, then sum up these items cost,
but no matter how I use sum of sumif, it will either calculate item 2 and item 6 twice, or it will only calculate if color string contains "Blue&Yellow&white"

anyone can help?

thanks

Hi instantreply and welcome to the forum,

I think you are looking for a newer function called SumIfS which can handle your harder question.

http://www.excelfunctions.net/Excel-...-Function.html

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Row\Col	A	B	C	D	E	F	G
1	items Name	Color	free	cost	Helper
2	item 1	red, blue, yellow	Y	100	Yes		196
3	Item 2	blue, yellow, black, white	Y	50	Yes
4	Item 3	red, yellow	N	20	Yes
5	Item 4	green	N	12	NO
6	item 5	green, red, blue	Y	22	Yes
7	item 6	White, red	Y	24	Yes

E2

Formula:

Please Login or Register  to view this content.

G2

Formula:

Please Login or Register  to view this content.

you could also use advanced filter and then simple SUM would do. see attachment and screenshot (it's national version of Excel, but in english only labels in advanced filter dialogs are different - fields remain the same