Need Help on Sorting + Counting + Categorising via Keywords

[maddude]

Hi All

I appreciate if you can help me with this. I've searched around the forums but can't seem to find a similiar solution.

I have a set of data in one row, green apples, red apples, china red apples, australia red apples, japan grapes, italy grapes, france grapes

The other row would be the quantity where I'll key in either 1 - 10, etc.

The problem now is that I'm using the COUNTIF function to sort out the grapes, & apples into 2 different categories but would like to sum up the total quantity of that particular category.

For example if japan grapes qty was 3, italy grapes was 2, and france grapes was 1, it would sum up into 1 category and column "grapes" "6"

Anyone have any ideas? Thanks!

 

[acw]

Hi

If the fruit description is in A2:A7, and the quantity in B2:B7 then try

=SUMIF(A2:A7,"*grapes",B2:B7)

Tony

 

[maddude]

Thanks a million man, hope you don't mind but how about if I need to auto categorise them into certain groups? What if I had 7 categories. "For example "China", "Indonesia", "France", "Italy",Germany","Australia","Mexico" and in my rows of data, I have "China Oranges" "France Grapes" & etc.

Is there a if else function in excel that can output the "category"? e.g China?

Thanks again mate!

 

[maddude]

Think I shall push this post up.

Hopefully someone would reply

 

[maddude]

Sorry guys but I've already tried the search function and couldn't find a solution. Any one out there? Thanks!

 

[Domenic]

Try...

=SUMPRODUCT(--(LEFT($A$2:$A$7,5)="China"),--(RIGHT($A$2:$A$7,6)="Grapes"),$B$2:$B$7)

Actually, here's one way that you can set up your spreadsheet...

Assumptions:

D1:F1 contains Apples, Grapes, and Oranges

C2:C4 contains China, France, and Italy

Formula:

D2, copied down and across:

=SUMPRODUCT(--(LEFT($A$2:$A$7,LEN($C2))=$C2),--(RIGHT($A$2:$A$7,LEN(D$1))=D$1),$B$2:$B$7)

Hope this helps!

 

[maddude]

Thanks a lot dude. I appreciate it But I have to apologise as I can't seem to catch it. My example question is something like the one below. Where in column S, it will match the product(in column R) from a predefined list of keywords from a particular category.

WMG2005fulfilment.xls
	R	S	T	U	V
25	Product	Country Of Origin	Category	China	Malaysia
26	China Oranges (Mand)	China	Keywords	Cn, Chinese, China	My, Malay, Malaysia
27	Au Strawberries	Australia
28	Malaysia Bananas	Malaysia
29	Fr Pears (Yellow)	France
30	Malaysia Pineapples	Malaysia
31	France Oranges (Large)	France
32	Cn Grapes Red	China
33	Durian D24 My	Malaysia
oct05

 

[maddude]

Anyone?

 

[Domenic]

Where are your quantities you want to sum? In addition to summing by country,do you also want to sum by fruit? More details are needed...

 

[maddude]

Hi Domenic, thanks for your reply. Row S would check the text in row R and determine which country of origin it is from. Once again, thanks!