Hi everyone,
I have a spreetsheet with one column which requests user to input their
amount. User wants to input ###us as us amount and without us as can amount.
Since that spreadsheet is too wide, they don't want to split this into two
column, but they want to summary them by currency, how
would I do this?
for example:

100
us100
100
us100

in one column
how can I sum us=200, can=200?

Thanks,
sarah

=SUM(IF(LEFT(A1:A100,2)="us",--RIGHT(A1:A100,3)))

entered with Ctrl + Shift + Enter since this is an array formula

for the canadian

=Sum(A1:A100)

--
Regards,
Tom Ogilvy

"sg" wrote in message
...
Hi everyone,
I have a spreetsheet with one column which requests user to input their
amount. User wants to input ###us as us amount and without us as can
amount.
Since that spreadsheet is too wide, they don't want to split this into two
column, but they want to summary them by currency, how
would I do this?
for example:

100
us100
100
us100

in one column
how can I sum us=200, can=200?

Thanks,
sarah

Hi Tom,

Your idea just worked perfectly for me.
Thanks so much,
Sarah
"Tom Ogilvy" wrote in message
...
=SUM(IF(LEFT(A1:A100,2)="us",--RIGHT(A1:A100,3)))

entered with Ctrl + Shift + Enter since this is an array formula

for the canadian

=Sum(A1:A100)

--
Regards,
Tom Ogilvy

"sg" wrote in message
...
Hi everyone,
I have a spreetsheet with one column which requests user to input their
amount. User wants to input ###us as us amount and without us as can
amount.
Since that spreadsheet is too wide, they don't want to split this into
two
column, but they want to summary them by currency, how
would I do this?
for example:

100
us100
100
us100

in one column
how can I sum us=200, can=200?

Thanks,
sarah