help with formula

  • Thread starter Thread starter Tarek
  • Start date Start date
T

Tarek

I hope I can get help with the mention below scenario:

Column A Column B ColumnC

Sales Rep Country Value
Mike France $16,565,530
Jane Italy $5,005,669
Nadia EU $13,356,343
homas Spain $12,872,515
Eric Germany, Austria $283,526
Mary Finland $531,841
Rami England $3,119,681
Ravi Netherlands $981,639
Derek Denmark, Poland $1,887,076
adeem Romania $1,552,580
Sum $42,800,057 (C11=sum(c1:c10)-c3)
EU=Germany + Austria + Finland + England + Netherlands + Denmark
Poland + Romania

The total country sum should not add the same country twice, and th
employee country assignment is not fixed (formula should not rely o
the employee as a reference). Is it possible to create a formula t
the required job?
Currently I am adding the countries manually (C11=sum(c1:c10)-c3)

Many Thanks in advance,
Tare
 
Hi Tarek

if i'm understanding you correctly, you want to add up all of the countries
excluding the value for the EU?

if so then this formula should work:

=SUMIF(B2:B11,"<>"&"EU",C2:C11)

Cheers
JulieD
 
Thanks Julie for your prompt reply I appreciate that. Your answer i
accurate if we have the same assignment always; however, next quarte
we may not have “EU” at all for instance:

ColumnA--------ColumnB----------------ColumnC
SalesRep-------Country------------------Value
Mike-------------Italy-----------------------$16,565,530
Jane-------------Italy-----------------------$5,005,669
Nadia------------Spain---------------------$13,356,343
homas-----------Spain---------------------$12,872,515
Eric---------------Germany, Austria-------$283,526
Mary-------------Finland--------------------$531,841
Rami-------------England------------------$3,119,681
Ravi--------------Germany-----------------$981,639
Derek------------Romania, Poland------$1,887,076
adeem-----------Romania----------------$1,552,580

a country can not be added twice, please advice,

Cheers!
Tare
 
Hi Tarek

but if a country can't be summed twice, which Italy or Spain do you want to
see in your total? and when you get to Romania or Germany it's even more
difficult as they're on their own and included with other countries.

What is the logic behind which figures get included?

Cheers
julieD
 
Sorry julie I included the wrong values per country. let's say
ColumnA--------ColumnB----------------ColumnC
SalesRep-------Country------------------Value
Mike-------------Italy-----------------------$20,000
Jane-------------Italy-----------------------$20,000
Nadia------------Spain---------------------$10,000
homas-----------Spain---------------------$10,000
Eric---------------Germany, Austria-------$7,000 (austria=2,000)
Mary-------------Finland--------------------$200
Rami-------------England------------------$500
Ravi--------------Germany-----------------$5,000
Derek------------Romania, Poland------$6,000 (poland - 5,000)
adeem-----------Romania----------------$1,000

The logic behind the scenario is to calculate: total countr
performance (with out calculating the same country twice) also sinc
assignments of sales rep changes every quarter, so formula should no
use the sales rep as a reference.

Regard,
Tare
 
Hi Tarek

if you can use two additional columns to the right of your data you can
extract the unique countries using the formula
=IF(COUNTIF($B$3:B3,B3)=1,B3,"")
(from www.cpearson.com/excel/duplicat.htm)
in the first blank column to the right of your existing data (say Column E)
then in column F you could use the following
=IF(LEN(E3)>2,C3,"")

and fill down and then at the end of that column you can
use the sum function to add all the values in this column HOWEVER this will
not extract the Austra or Poland bits for you - i can't think of how you can
do this automatically.

so you would then end up with


ColumnA ColumnB ColumnC
SalesRep Country Value
Mike Italy $20,000
Italy 20000
Jane Italy $20,000
Nadia Spain $10,000
Spain 10000
homas Spain $10,000
Eric Germany, Austria $7,000 (austria=2,000) Germany, Austria
7000

etc


49700

hope this helps somewhat - and possibly someone else will be able to come up
with a better solution.

Cheers
JulieD
 
Back
Top