Identify currency and do sum

S

sg

Hi everyone,
I have a spreetsheet with one column which requests user to input their
amount, either can$ or us$, but I want to summary them by currency, how
would I do this?
for example:

100
us100
100
us100

how can I sum us=200, can=200?

Thanks,
sarah
 
F

Frank Kabel

Hi
first try splitting tis information in two columns:
-Column A: amount
- Column B: Currency code (e.g. USD, CAN)

Now you also have to add the conversion factor (e.g. in column C).
After this use
=SUMPRODUCT(A1:A10,C1:C10)
 
S

sg

Hi Frank,

thank you for you reply. Since the spreadsheet is too wide, user doesn't
want to split them into one column, that's the problem. And I realized this
might be something to do with programming and submitted a thread in
programming group as well. Of course if you know the answer, I would
appreicate it.

Thanks,
Sarah
 
S

sg

Another question is that how I can pick 100 from 100us using function.
Thanks,
sarah
 
F

Frank Kabel

Hi Sarah
It is really not a good idea to store text and numbers within one cell.
You'll have to use more complicated formulas. Trust me this creates
more problems with your later processing. also you need a table which
stores the currency conversion factors somethere
 
A

AlfD

Hi!

I haven't got a handle on how much space matters (is it screen or prin
space: would hidden columns be OK?)
The first thing to say would (as Frank hinted, you could do with
better data structure.
However: make a start by noting that the sum of a column of mixe
numerical values and string constants is the sum of only the numerica
values.
So Sum(A1:A4) (if your 4 values are in A1 to A4) would give 200 - th
sum of just the can$.
Now I can get you an answer to the us$ sum. To get it, we can add al
of the amounts and subtract the can$ total.
To do that (and do it on a workbook you have copied, in case you make
slip), put a sum formula for the whole column somewhere convenient. Mak
a note (non-volatile) of the can$ total. Now use find > replace to tak
out the "us" prefixes: find us : replace with nothing.

You overall total should now pop up. Press Undo to revert to th
position with "us" still there.

It would stand a bit of tidying up, but it does the job in situ whic
seems to be important.

I just hope there is a better way!

Al
 
S

sg

Thanks Frank and AlfD. I know this is rddiculous or silly to do so, I did
suggested and got rejected. I found an answer in programming group using the
formular below:

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

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

Thanks again for your help.
Sarah
 
Top