how to totalize only column value from certain accounting -> Currencyformat

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

For a sheet with invoices I need to totalize column values from a
different currency (cell format Accounting). How to perform? All cells
can have any currency USD or EUR.


Column A Column B:

USD 5,000.00
EUR 3,765,00
USD 100.00

Total USD 5,100.00
Total EUR 3,765.00

Thank you,

Bart
Excel 2003
 
Thanks dave, I used your SUMIF statement.
I another cell I need to mention whether payment has occurred.

I that case the are two conditions: currency (as above) and a second
one: whether the neighboring cell has been filled in (payment date).
If it is left empty there must be no total sum process.

My question how to get the formula the contains both conditions?

Bart
 
Try this:

=SUMPRODUCT(--(A1:A50="USD"),--(C1:C50<>""),(B1:B50))

assuming payment date is in column C - adjust the ranges to suit.

Hope this helps.

Pete
 
Thanks, Pete, for the multi-condition approach. I'll use that.
I would probably have on my sheet another column:
column D is "amount paid": =IF(C1="",0,B1)
then the sum of those is the simpler: =SUMIF(A1:A50,"=USD",D1:D50)
 
More feedback -- love these 'magic' formulae.
Looking at the SUMPRODUCT help (XL97):
"Using array [formula]s provides a more general solution
for doing operations similar to SUMPRODUCT"
so that =SUMPRODUCT(A1:A50,B1:B50)
could be {=SUM(A1:A50*B1:B50)}
and your =SUMPRODUCT(--(A1:A50="USD"),--(C1:C50<>""),(B1:B50))
could be {=SUM((A1:A50="USD")*(C1:C50<>"")*B1:B50)}
(where, of course, { } means use Control-Shift-Enter)
 
Related to this, I need some assistance of my other question

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/9117d8b92b0e0b51?hl=en

http://groups.google.com/group/microsoft.public.excel/topics?hl=en&start=20&sa=N

How to get this solved?

Bart

More feedback -- love these 'magic' formulae.
Looking at the SUMPRODUCT help (XL97):
"Using array [formula]s provides a more general solution
for doing operations similar to SUMPRODUCT"
so that =SUMPRODUCT(A1:A50,B1:B50)
could be {=SUM(A1:A50*B1:B50)}
and your =SUMPRODUCT(--(A1:A50="USD"),--(C1:C50<>""),(B1:B50))
could be {=SUM((A1:A50="USD")*(C1:C50<>"")*B1:B50)}
(where, of course, { } means use Control-Shift-Enter)

Pete_UK said:
Glad to be of help, Dave - thanks for feeding back.
Pete
Click to expand...
Click to expand...
 
I suggest you start a new thread, something like

Sub: Using multiple currencies
"How can I format some cells for USD (dollars) and other cells
for EUR (euros)?"

But maybe I don't understand your post.
 
Back
Top