Bank Reconciliation Sum If

Hi Everyone!

I am trying to write an SumIf for the attached file. My objective is to Sum the totals in Col H and I ( Col H - Col I = Net Amount) if Col E is the same number and then return the results.

Thank you,
JP

Does somethign like this work for you?
=SUMIF(E:E,55335,H:H)-SUMIF(E:E,55335,I:I)

You can put this formula in J1:

=IF(COUNTIF(E$1:E1,E1)=1,SUMIFS($H:$H,E:E,E1)-SUMIFS($I:$I,E:E,E1),"")

then copy down.

Hope this helps.

Pete

Try

=SUMPRODUCT((E1:E43=55335)*((H1:H43)-(I1:I43)))

or

=SUMPRODUCT((E1:E43=M1)*((H1:H43)-(I1:I43)))

M1= 55335

Wow, yes, this works. Now in Col E these are batch numbers; which I have a report that contains all of these numbers. How could I incorporate a look up chart of these numbers like an array? I am trying to prevent from manually typing each of the batch #'s in Col E in each SUMIF statement.

Thank you again for the fast reply!

=SUMPRODUCT((E1:E43=A2)*((H1:H43)-(I1:I43)))


If the batch number are in (say) A2:A10 and the formula in C2:C10 then use the above formula and drag down rows C2:C10

Did you try my formula in J1? It gives exactly what you show in your example file - do you need a different layout?

Pete

Hi JohnTopley!

I just tried out your formula and it just returns the good ol #Value!. I entered the batch number in Col A cell 2, formatted in 2 ways one as a number and I also tried as general. I also checked to make sure the rest of the data fell into the range of the formula, but it still returned #Value!

Hi Pete_UK!

Yes, I did try your formula, however it returned a zero value.

In Sheet2 of your sample file

A2=55335

=SUMPRODUCT((Sheet1!$E$1:$E$43=A2)*((Sheet1!$H$1:$H$43)-(Sheet1!$I$1:$I$43)))

gave the correct result.

dosydos,

Your formula works great, is it possible I could add a table of data for the batch numbers, rather than entering each batch number in manually within the formula? For example, =SUMIF(E:E,55335,H:H)-SUMIF(E:E,55335,I:I)
In the selection could I point this to a table that would have say a list of batches 55335,55336,55337, etc.?

As per other formulae ....

=SUMIF(E:E,A2,H:H)-SUMIF(E:E,A2,I:I)

JphnTopley!

Sorry, I went bacl to the example file and it does work. Perhaps in my live file I did not have it referenced correctly. Would it be easy to eliminate the duplicated returned amounts? For example it will return the amount $47,690.17 as many times as there are the batch numbers we told it to return. Can this be limited to only be returned 1 time for the batch? Like How I manually returned the value 1 time in Col J for this entire batch? Just curious. Sorry to be such a pain in the neck.

Originally Posted by japj1984

Hi Pete_UK!

Yes, I did try your formula, however it returned a zero value.

In the attached file I put my formula in L1 and copied down, so you can compare it directly with the values that you have in column J.

Hope this helps.

Pete

Thank you Pete_UK!