Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and nested Sumif error
Hi
I am trying to automate my bank reconcilliation programme, Column F = Receipts Column G = Payments Column I = Current Balance (book Balance) Column J = Item cleared Bank (=3 formated to a Tick) Column K = Current Bank Balance My formula is =IF(J175<3,SUMIF(J$2:J175,"=3",F$2:G175),0) The formula is accepted by excel as valid, but the result only includes items in column F, column G is ignored. I could overcome this with a hiden column which is the sum of F & G, but this is not advisable on a sheet with so many line. The formulas almost there, I would just like to complete it Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and nested Sumif error
Try:
=IF(J175<3,SUMIF(J$2:J175,3,F2:F175)+SUMIF(J2:J17 5,3,G$2:G175),0) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Joco" wrote in message ... Hi I am trying to automate my bank reconcilliation programme, Column F = Receipts Column G = Payments Column I = Current Balance (book Balance) Column J = Item cleared Bank (=3 formated to a Tick) Column K = Current Bank Balance My formula is =IF(J175<3,SUMIF(J$2:J175,"=3",F$2:G175),0) The formula is accepted by excel as valid, but the result only includes items in column F, column G is ignored. I could overcome this with a hiden column which is the sum of F & G, but this is not advisable on a sheet with so many line. The formulas almost there, I would just like to complete it Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and nested Sumif error
TRY
=IF(J175<3,SUMPRODUCT((J2:J175=3)*(F2:G175))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If and nested Sumif error
"excelent" wrote: TRY =IF(J175<3,SUMPRODUCT((J2:J175=3)*(F2:G175))) Formula works great I now have to different formulars which work. I have been able to delete three colomns of formular on each page, there are 23 pages and each has over 300 lines Thanks again Joco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"How do I correct error in Excel2003 for nested subtotal?" | Excel Discussion (Misc queries) | |||
Nested SumIf statement | Excel Worksheet Functions | |||
How do I set up a nested SUMIF formula for a number of variables? | Excel Worksheet Functions | |||
Nested IF AND OR function Inexplicable error | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions |