how to totalize only column value from certain accounting -> Currency format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 19 Jan 2008 05:59 GMT

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

Reply to this Message

Gary''s Student - 19 Jan 2008 12:53 GMT

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

similar for euros

Signature

Gary''s Student - gsnu2007c

> For a sheet with invoices I need to totalize column values from a
> different currency (cell format Accounting). How to perform? All cells
[quoted text clipped - 13 lines]
> Bart
> Excel 2003

Reply to this Message

Dave D-C - 19 Jan 2008 20:58 GMT

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

>For a sheet with invoices I need to totalize column values from a
>different currency (cell format Accounting). How to perform? All cells
[quoted text clipped - 12 lines]
>Bart
>Excel 2003

Reply to this Message

AA Arens - 20 Jan 2008 04:36 GMT

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

> e.g. for USD: =SUMIF(A1:A3,"=USD", B1:B3)
>
[quoted text clipped - 14 lines]
> >Bart
> >Excel 2003

Reply to this Message

Pete_UK - 20 Jan 2008 13:26 GMT

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 dave, I used your SUMIF statement.
> I another cell I need to mention whether payment has occurred.
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Dave D-C - 20 Jan 2008 16:22 GMT

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)

>Try this:
>=SUMPRODUCT(--(A1:A50="USD"),--(C1:C50<>""),(B1:B50))
[quoted text clipped - 32 lines]
>>
>> - Show quoted text -

Reply to this Message

Pete_UK - 20 Jan 2008 17:07 GMT

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

Pete

> Thanks, Pete, for the multi-condition approach. I'll use that.
> I would probably have on my sheet another column:
[quoted text clipped - 39 lines]
>
> - Show quoted text -

Reply to this Message

Dave D-C - 20 Jan 2008 21:16 GMT

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
[quoted text clipped - 42 lines]
>>
>> - Show quoted text -

Reply to this Message

AA Arens - 21 Jan 2008 10:58 GMT

Related to this, I need some assistance of my other question

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/9117d
8b92b0e0b51?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):
[quoted text clipped - 52 lines]
>
> >> - Show quoted text -

Reply to this Message

Dave D-C - 21 Jan 2008 15:11 GMT

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.

>Related to this, I need some assistance of my other question

>How to get the cell account formatting .. in a certain currency
>that I choose ..?

Reply to this Message

AA Arens - 22 Jan 2008 07:48 GMT

> I suggest you start a new thread, something like
>
[quoted text clipped - 8 lines]
> >How to get the cell account formatting .. in a certain currency
> >that I choose ..?

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

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/5e687
afad3787038?hl=en#68db938c20477244

Thanks.

Bart

Reply to this Message