finding text and displaying related values

[reedburg]

I'm having alot of trouble in excel 2007.. my task is to find the companies that are in australia, u.s.a., u.k., germany and japan, and then to list their "total revenue" in a seperate column, by using only a formula.

Could somone please help me? i attached the xls and a screenshot too.

thanks

[daddylonglegs]

This is a homework assignment?

Usually we'll give you some pointers.......but not the answer. What have you tried?

[reedburg]

Yes it's for uni. I tried to use something along the lines of ...


=IF(FIND(OR(Australia,Germany,U.S.A.,U.K.,Japan),D3,1)F3,"0")

I think that's what i tried.. Anyway i spent at least an hour on it and kept getting errors.. usually #NAME or #VALUE

Thanks.

[daddylonglegs]

OK, you are going along the right lines but firstly you'd need to use quotes around all the countries.....

If you then use FIND with an "array constant" including all the countries, i.e.

=FIND({"Australia","Germany","U.S.A.","U.K.","Japan"},D3)

Then that will return an array consisting of either numbers (when the country is found) or an error (#VALUE!) when the country isn't found, so you need a way to determine whether there are any numbers in the array....and a way which won't fail when it finds error values. For that you can use COUNT.......so your final formula might be

=IF(COUNT(FIND({"Australia","Germany","U.S.A.","U.K.","Japan"},D3))>0,F3,0)

You could probably go one step further and produce a formula with one grand total, which doesn't involve summing column N. That might involve SUMIF function and some wildcards.....

[reedburg]

Thanks for your help, ill try that.

[reedburg]

Still having a bit of trouble with it.. I keep getting errors. As you can probably tell my excel skills are very limited, and i cant really decipher what you've given me. Are you just giving me a hint or is that formula supposed to work?

[daddylonglegs]

If you paste the formula into N3 and copy down it should give the results you want.....