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

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

If and nested Sumif error
 

TRY
=IF(J175<3,SUMPRODUCT((J2:J175=3)*(F2:G175)))

If and nested Sumif error
 

Hi Sandy

You formula works a treat I have now been able to delete three columns of
formulars, on each page, there are 23 pages, and over 300 lines on each
my files a fraction of the size

Thanks Joco

"Sandy Mann" wrote:

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

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