Hi,
I'm trying to get a list of balances for different account numbers for
different currencies. Some accounts have different currencies however and so
they need to satisfy the conditions of being both a certain currency and a
certain account number. This is the latest I've got so far, although there
have been different versions as I've tried almost everything:

=SUMIF(('2007'!C4:C2000,'2007'!K4:K2000),("currenc y",'account
#'),'2007'!E4:E2000)

I have tried using nested IF statements also without any luck. Can anyone
help me?

sumproduct would probably do what you want
I do not know what your setup is but
=sumproduct)--(account-number-range = account number),--(currency-range =
currency),value range)
the arrays must be the same size and you cannot use A:A to reference a full
column. A1:A64000 would be OK.


"mishmash" wrote:

Hi,
I'm trying to get a list of balances for different account numbers for
different currencies. Some accounts have different currencies however and so
they need to satisfy the conditions of being both a certain currency and a
certain account number. This is the latest I've got so far, although there
have been different versions as I've tried almost everything:

=SUMIF(('2007'!C4:C2000,'2007'!K4:K2000),("currenc y",'account
#'),'2007'!E4:E2000)

I have tried using nested IF statements also without any luck. Can anyone
help me?

You can do like this :

supose in range A11:C16 you have :

Currency Acount Value
3 1 7
3 2 15
1 1 14
2 2 23
3 1 21

The formula to get the sum of values with currency=3 and Acount=1 will
be

=SUM(IF((A12:A16=3)*(B12:B16=2);C12:C16;""))

This is a array formula so when entering the formula you must do Ctrl
Shift Enter

Regards

This was really helpful. Many thanks

" wrote:

You can do like this :

supose in range A11:C16 you have :

Currency Acount Value
3 1 7
3 2 15
1 1 14
2 2 23
3 1 21

The formula to get the sum of values with currency=3 and Acount=1 will
be

=SUM(IF((A12:A16=3)*(B12:B16=2);C12:C16;""))

This is a array formula so when entering the formula you must do Ctrl
Shift Enter

Regards

Try this:

=SUMPRODUCT((E7:E350="ARCOM")*(--T7:T350=1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"mishmash" wrote:

Hi,
I'm trying to get a list of balances for different account numbers for
different currencies. Some accounts have different currencies however and so
they need to satisfy the conditions of being both a certain currency and a
certain account number. This is the latest I've got so far, although there
have been different versions as I've tried almost everything:

=SUMIF(('2007'!C4:C2000,'2007'!K4:K2000),("currenc y",'account
#'),'2007'!E4:E2000)

I have tried using nested IF statements also without any luck. Can anyone
help me?

Apologies....This has nothing do to with your post.


***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

=SUMPRODUCT((E7:E350="ARCOM")*(--T7:T350=1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"mishmash" wrote:

Hi,
I'm trying to get a list of balances for different account numbers for
different currencies. Some accounts have different currencies however and so
they need to satisfy the conditions of being both a certain currency and a
certain account number. This is the latest I've got so far, although there
have been different versions as I've tried almost everything:

=SUMIF(('2007'!C4:C2000,'2007'!K4:K2000),("currenc y",'account
#'),'2007'!E4:E2000)

I have tried using nested IF statements also without any luck. Can anyone
help me?