Summarize mixed-currency reports: group, export and check every source row

Raw materials: four rows reveal an invalid total

These are fictional teaching records, not customer orders or accounts. amount is signed and currency must be explicit. R02 is already a −20 refund; do not subtract it again because its note says refund. R04 has an unknown currency, not an implied USD value copied from its neighbors.

Start from four complete rows, not just the AI summary

The ZIP includes source.csv, blank-amount.csv, expected-groups.csv, review-ledger.csv, a clearly hypothetical conversion record and bilingual steps. Use source.csv for the normal run. Source amounts remain strings in CSV until a numeric operation validates them; a currency label does not convert an amount.

record_id,currency,amount,note
R01,USD,100,receipt
R02,USD,-20,refund_already_signed
R03,EUR,50,receipt
R04,,40,currency_unknown

Request groups and exceptions before a grand total

The task is organization and explanation, not selecting an exchange rate. Retain fields and original values and request calculations and exception destinations. Follow your organization’s upload rules for actual business files; this fictional input contains no customer data.

Fictional teaching data:
record_id,currency,amount,note
R01,USD,100,receipt
R02,USD,-20,refund already signed
R03,EUR,50,receipt
R04,,40,currency unknown
Retain every original value. Group signed amount by explicit currency, reporting each group’s record count and calculation. List blank currency separately as unresolved. Do not guess or convert automatically. The refund is already negative; do not subtract it twice. Explain why there is no single-currency total. Leave unspecified exchange rates and dates blank.

The complete answer is two subtotals and one exception

The USD group contains R01 and R02: two rows, 100+(−20)=80. The EUR group contains R03: one row totaling 50. R04 retains 40 with an unknown currency. Reconcile records as 2+1+1=4. Deleting the exception does not establish that all amounts were handled.

For a spreadsheet cross-check, Microsoft’s SUMIF documentation supports summing a range using a corresponding criterion. With currencies in B2:B5 and amounts in C2:C5, =SUMIF(B2:B5,"USD",C2:C5) gives 80. Check numeric cell types, complete ranges and unknown currencies separately; one formula cannot create a full audit.

Group by currency, then reopen the downloaded CSV

Open Data summary, import source.csv and confirm that the current working data has four rows. This local grouping operation does not look up exchange rates or identify a missing currency. Keep the original file separate from the derived summary.

1. 1
   In “Group summary (current working data)”, choose currency as Grouping field and amount as Sum / numeric validation field. Click Group summary. Do not select record_id as the grouping key.
2. 2
   Expect three groups in the result: USD count 2 total 80; EUR count 1 total 50; blank currency count 1 with raw amount total 40. The blank group is retained by the tool, not automatically labelled or accepted as a known currency.
3. 3
   Download CSV and reopen it. The headers are Group, Count and Total in English. Compare by group value, not row order; a blank first field in one result row must stay visible in your review ledger.
4. 4
   Check source coverage separately: R01 and R02 feed USD, R03 feeds EUR, R04 remains unresolved. Counts add to four. Keep the signed refund once; do not subtract it again because note says refund.

Do not sum the Total column into a cross-currency amount or average the currency-group totals. This source also has no reporting-period field; sending it directly into a period-growth comparison would leave the date contract undefined.

State direction and scope before converting

For this exercise only, assume a fictional rate of 1 EUR=1.10 USD for known-currency rows, with no fees. This is not a current rate or financial-accounting advice. 50 EUR becomes 55 USD; with the original 80 USD, the known-currency subtotal is 135 USD. R04 remains unresolved and excluded.

Calculating 50÷1.10 reverses this particular quote. Multiplying euros by dollars per euro cancels units to dollars. A quote stating euros per dollar requires a different formula. Do not copy a generic instruction to multiply by the rate. Real work also requires the rate source, applicable date, fees and organizational rules.

Test an amount failure without confusing it with missing currency

In a fresh page, import blank-amount.csv. It changes only R03 amount from 50 to blank. Run the same currency grouping and amount sum. The current tool should stop with a blank/nonnumeric amount error; it must not silently report EUR 0. A successful count-only run would answer a different question and cannot repair the missing amount.

For this exercise, recover by reimporting source.csv, whose R03 value 50 is known from the preserved original, and rerun for 80, 50 and unresolved 40. In real work, an unknown source amount needs confirmation; copying the exercise answer would fabricate evidence.

Repair each error instead of pressing SUM again

Treating a blank currency as zero confuses an unknown unit with no amount. Follow the missing-versus-zero audit and retain the exception. Combining currencies needs regrouping; deducting a refund twice needs a return to the signed source value.

Before comparing periods, align currencies, exchange-rate policy and record scope, then discuss growth. The percentage and denominator case shows how a polished rate can still compare different populations.