2 Replies Latest reply on Aug 21, 2012 12:17 PM by Eric Wilding

# Not able to calculate totals using IF...THEN...ELSE

I am trying to create the table below that shows distinct counts of person ID's by course delivery methods, excluding certain subjects. However, I cannot get the totals as shown below.

 Delivery Distinct Count of ID's W Delivery without AVSC Subject 526 W Delivery with AVSC Subject 9 Total W Delivery Code (regardless of subject) 530 V Delivery Code without AVSC Subject 149 V Delivery Code with AVSC Subject 47 Total V Delivery Code (regardless of subject) 192

I am using the following IF...THEN...ELSE to calculate the delivery variables.

IF [DELIVERY_CODE] = 'W' AND [SUBJECT] <> 'AVSC' THEN 'W - without AVSC'

ELSEIF [DELIVERY_CODE] = 'W' AND [SUBJECT] = 'AVSC' THEN 'W - with AVSC'

ELSEIF [DELIVERY_CODE] = 'W' THEN 'W'

ELSEIF [DELIVERY_CODE] = 'V' AND [SUBJECT] <> 'AVSC' THEN 'V - without AVSC'

ELSEIF [DELIVERY_CODE] = 'V' AND [SUBJECT] = 'AVSC' THEN 'V - with AVSC'

ELSEIF [DELIVERY_CODE] = 'V' THEN 'V'

ELSE 'Other'

END

...And below are the results...Which matches the above but there are no totals. How on earth do I do this? In my example twbx I have greatly simplified my data into just three delivery types (W, T, V) and six subjects, thus making the data set small and manageable. I needs to see W delivery type courses with and without the AVSC subject AND also total W delivery type courses regardless of any of the available subjects. The same with V delivery types.

Calculation1    Distinct count of ID

W - without AVSC    526

W - with AVSC    9

V - without AVSC    149

V - with AVSC    47

Other    104

Thanks

Eric

• ###### 1. Re: Not able to calculate totals using IF...THEN...ELSE

Hi Eric,

The way that the calculation is set up is when the W's with and without AVSC (same with the V's) define the total W and therefore, W as a total won't display at all because it is already accounted for. Instead of creating just one calculation, multiple calculations can be created to get the desired results. I have reattached your workbook.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Not able to calculate totals using IF...THEN...ELSE

Oh, I see! Thanks for the explanation and the help.