Using Distinct to sum multiple data entries

[#1]

Hi there

I have some data that looks like this

HESA	Project Code	Application Value	Staff Name
Charity	PR1	1000	Dave
Charity	PR1	1000	Keith
Charity	PR1	1000	Bruce
Research Council	PR2	5000	Bruce
Research Council	PR2	5000	Dave

My requirement is to produce the application value for each HESA classification. The problem is that the total for PR1 is actually 1000 (not 3000 by simple sum). It is repeated for each staff member involved.

Basically I need an expression that only sums one value for each project code. I think I can use Distinct in the sum formula for this, but cannot seem to produce a valid expression.

Can anyone help?

[#2]

Hi,

See attach file if it's what you're looking for

[#3]

Hi, thank you.

From the data I gave that would work, but what about this

HESA	Project Code	Application Value	Staff Name
Charity	PR1	1000	Dave
Charity	PR1	1000	Keith
Charity	PR1	1000	Bruce
Research Council	PR2	5000	Bruce
Research Council	PR2	5000	Dave
Charity	PR3	1000	Steve
Charity	PR3	1000	Diane

I need to get the answer 2000 for Charity and 5000 for Research Council.

Sorry to add to the query, but realised a loop hole in the data and question.

Regards

Rob

[#4]

A:

LOAD Distinct HESA,

     PROJECTCODE,

     A_VALUE ,

     S_NAME

FROM

(ooxml, embedded labels, table is Sheet1) ;

THEN pivot chart---

dimension---     hesa

                         projectcode

expression---     SUM( DISTINCT A_VALUE)

then output like this--

HESA	PROJECTCODE	SUM( DISTINCT A_VALUE)
C	PR1	1000
C	PR3	1000
R	PR2	5000