Sumproduct or Sumif formula
 

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter

Sumproduct or Sumif formula
 

Assuming your data is from A2 to C7, try the below

D2 = AccountNo
E2 = month end date

=SUMPRODUCT(--(A2:A7=D2),--(B2:B7=E2),--(C2:C7))

If this post helps click Yes
---------------
Jacob Skaria


"Peter" wrote:

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter

Sumproduct or Sumif formula
 

=SUMPRODUCT(--(A2:A7="INTE08"),--(G2:G7=DATE(2009,2,28)),C2:C7)


Regards,
Stefi

€žPeter€� ezt Ã*rta:

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter

Sumproduct or Sumif formula
 

You need to have the date columns in the same format ColB and cell E2..
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Assuming your data is from A2 to C7, try the below

D2 = AccountNo
E2 = month end date

=SUMPRODUCT(--(A2:A7=D2),--(B2:B7=E2),--(C2:C7))

If this post helps click Yes
---------------
Jacob Skaria


"Peter" wrote:

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter

Sumproduct or Sumif formula
 

Hi Stefi

Thank you for this - I was able to change the ="account" etc to cell range
and works fine.

I must have had brackets in wrong place - as fairly straight forward.

thanks very much for your help

"Peter" wrote:

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter

Sumproduct or Sumif formula
 

You are welcome! Thanks for the feedback!
Stefi

€žPeter€� ezt Ã*rta:

Hi Stefi

Thank you for this - I was able to change the ="account" etc to cell range
and works fine.

I must have had brackets in wrong place - as fairly straight forward.

thanks very much for your help

"Peter" wrote:

Hi all

I am looking for a formula that looks at col a and col b and where the same
total col c.

Account No Month End Date Balance
INTE08 28/02/2009 £1,000.00
INTE08 28/02/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE08 31/03/2009 £1,000.00
INTE07 30/04/2009 £1,000.00
INTE07 30/04/2009 £1,000.00

So - where Account No and Month End = Inte08 and 28/2/09 then total balance
= £2000.

I have had a quick look at sumproduct formula but keep getting Value#
(array) or non array.

Any ideas would be greatly appreciated

Thanks

Peter