Summary of Unique item values

[Robert Hatcher]

I need to summarize unique data from three columns on a worksheet;
“item” ,“Cost” and “Currency”. “Item” is the name of the expense,
“Cost” is the amount in the corresponding currency and the “Currency”
column lists the type of currency used: USD, Euro, CAND etc. There
may be multiple entries of each currency type.
I need to list each currency type used and summarize the total cost
in each currency type.
I was working with SUM(IF(RngCurrency="Euro",RngCost)) entered as an
array formula and that summarized the Euro costs. But I have got
nowhere trying to take it to the next step and have it list each
currency type with the corresponding summary of costs.
The results are dynamic as each Event (one worksheet per event) may
have several currencies
Also I need to avoid VBA in this case because the file will be sent
and used at government sites where we have a hard to getting macros
accepted.
Any help will be appreciated
Robert

 

[zvkmpw]

I used row 1 as a header row, and put Item, Cost, and Currency in
columns A, B, C.

Column E will have the unique currency types.

Column F will have the corresponding totals.

Column D is a helper column to extract the unique currency types. It
can be hidden later to avoid clutter.

In D2 I put
=IF(COUNTIF(C$1:C1,C2)=0,MAX(D$11)+1,"")

In E2 I put
=IF(ROW()>MAX(D)+1,"",
OFFSET(C$1,MATCH(ROW()-1,D,0)-1,0))

In F2, I put
=IF(E2="","",SUMIF(C:C,E2,B:B))

Then I copied D2:F2 downward for the length of the list.

This seems to get the desired result, if I understand the problem
correctly.

Hope this helps.

 

[Robert Hatcher]

I used row 1 as a header row, and put Item, Cost, and Currency in
columns A, B, C.

Column E will have the unique currency types.

Column F will have the corresponding totals.

Column D is a helper column to extract the unique currency types. It
can be hidden later to avoid clutter.

In D2 I put
  =IF(COUNTIF(C$1:C1,C2)=0,MAX(D$11)+1,"")

In E2 I put
  =IF(ROW()>MAX(D)+1,"",
     OFFSET(C$1,MATCH(ROW()-1,D,0)-1,0))

In F2, I put
  =IF(E2="","",SUMIF(C:C,E2,B:B))

Then I copied D2:F2 downward for the length of the list.

This seems to get the desired result, if I understand the problem
correctly.

Hope this helps.

Thanks, That works. The arrangment of the data isnt what Im after but
I can create a report sheet linked to the data and arrang it...
Thanks again
Robert