| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif, SumProduct, CountIf Formula Help | Excel Discussion (Misc queries) | |||
| sumif/sumproduct formula help | Excel Worksheet Functions | |||
| sumproduct or sumif formula help | Excel Discussion (Misc queries) | |||
| Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) | |||
| SumIf/SumProduct Formula Help | Excel Worksheet Functions | |||
Linear Mode
