9 Replies Latest reply: Oct 13, 2011 4:44 AM by julierizkallah

# Sum records based on a condition

Hi!

I would like to Sum the [# sent] and [# sent on time] for each country when I select specific months. For the below example, if I select Jan-11, Feb-11, and Mar-11, I need to have the total for each country according to the months selected.

Original table is

 Country Month # sent # sent on time % on time Country A Jan-11 4 4 100.00 Country A Feb-11 5 4 80.00 Country A Mar-11 3 3 100.00 Country B Jan-11 7 7 100.00 Country B Feb-11 2 2 100.00 Country B Mar-11 8 7 87.50 Country C Jan-11 5 5 100.00 Country C Feb-11 10 10 100.00 Country C Mar-11 1 1 100.00 Country D Jan-11 4 4 100.00 Country D Feb-11 9 8 88.89 Country D Mar-11 7 7 100.00

I need the totals to figure in a table as per the below. the percentage is recalculated based on the new numbers.

 # sent # sent on time % on time Country A 12 11 91.67 Country B 17 16 94.12 Country C 16 16 100.00 Country D 20 19 95.00

I would highly appreciate your help,

Regards,
Julie

• ###### Sum records based on a condition

Just create a straight or Pivot table with the only dimension= Country

sum(sent#)

Sum(sentontime#)

• ###### Re: Sum records based on a condition

Hi,

Use pivot table and create a chart like add sum of values

See the attached sample file

Rgds

Anand

• ###### Re: Sum records based on a condition

here is the exact result you want

see the attached file

• ###### Sum records based on a condition

Thank you .

• ###### Re: Sum records based on a condition

hello julierizkallah ,

did you check my post ,is it wrong ?

• ###### Re: Sum records based on a condition

At the contrary. It was very correct. But i could only post Correct Answer one time

• ###### Sum records based on a condition

i am not bother but  you should check on first come first serve basis .

• ###### Re: Sum records based on a condition

It's my first time that i post there so i thought i could mark all correct answers. Next time i will do for sure

• ###### Re: Sum records based on a condition

Hi,

Use

Dimension -> Country

Expression1 -> sum([# sent])

Expression2 -> Sum([# sent on time])

Expression3 -> Sum([# sent on time]) / sum([# sent]) and set decimal place 2 and fixed to 2 and show in % in chart properties

See the update file with percentage, see the pivot chart.

Rgds

Anand