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!

 

[Domenic]

Okay, I think I understand...

Let U26:V37 contain the following table...

Au	Australia
Australian	Australia
Australia	Australia
Cn	China
Chinese	China
China	China
Fr	France
French	France
France	France
My	Malaysia
Malay	Malaysia
Malaysia	Malaysia

Then enter the following formula in S26 and copy down:

=INDEX($V$26:$V$37,MATCH(TRUE,ISNUMBER(FIND(" "&$U$26:$U$37&" "," "&R26&" ")),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[maddude]

HI Domenic, once again I appreciate your help. Thanks a lot! If ya drop by Singapore and need a food tour, let me know!

 

[maddude]

Oh man, I got #N/A for certain fields, is it because they were done on different OS?

 

[maddude]

I've got it solved somehow.. I changed all the fields in which it needs to check to lowercase and it worked! STRANGE....

 

[Domenic]

I've got it solved somehow.. I changed all the fields in which it needs to check to lowercase and it worked! STRANGE....

The formula is case-sensitive. If you don't want the formula to be case-sensitive, replace FIND with SEARCH.

HI Domenic, once again I appreciate your help. Thanks a lot!...

You're very welcome! Glad I could help!