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

[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

 

[Gary''s Student]

=SUMPRODUCT(--(A1:A3="USD"),(B1:B3))

similar for euros

 

[Dave D-C]

e.g. for USD: =SUMIF(A1:A3,"=USD", B1:B3)

 

[AA Arens]

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

 

[Pete_UK]

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

 

[Dave D-C]

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",D150)

 

[Pete_UK]

Glad to be of help, Dave - thanks for feeding back.

Pete

 

[Dave D-C]

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)

 

[AA Arens]

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)

Glad to be of help, Dave - thanks for feeding back.
Pete

 

[Dave D-C]

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.

 

[AA Arens]

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.

Indeed better, here is the url. I have explained more clearer.

http://groups.google.com/group/micr...hread/5e687afad3787038?hl=en#68db938c20477244

Thanks.

Bart