Need Help on Sorting + Counting + Categorising via Keywords

New Member
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!

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

acw

MrExcel MVP
Hi

If the fruit description is in A2:A7, and the quantity in B2:B7 then try
Code:
``=SUMIF(A2:A7,"*grapes",B2:B7)``

Tony

New Member
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!

New Member
Think I shall push this post up.

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

Domenic

MrExcel MVP
Try...

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

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!

New Member
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
RSTUV
25ProductCountry Of OriginCategoryChinaMalaysia
26China Oranges (Mand)ChinaKeywordsCn, Chinese, ChinaMy, Malay, Malaysia
27Au StrawberriesAustralia
28Malaysia BananasMalaysia
29Fr Pears (Yellow)France
30Malaysia PineapplesMalaysia
31France Oranges (Large)France
32Cn Grapes RedChina
33Durian D24 MyMalaysia
oct05

Anyone?

Domenic

MrExcel MVP
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...

New Member
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!

Replies
5
Views
431
Replies
1
Views
174
Replies
18
Views
503
Replies
1
Views
954
Replies
1
Views
362

1,195,650
Messages
6,010,915
Members
441,572
Latest member
keobongda8812

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back