formula to calculate total amount of previous invoice

good evening all
kindly can help how can calculate previous total amount of invoice
in my attached excel
in cell P2 its contain project name that can i selected from drop list then column Q2 have list of invoiced issued for this project before (column I) ,matched with selection in cell P2

result will be like in R2 for total amount of previous invoiced And S2 for current balance
i make example result in different line in column P,Q,R,S to explain how calculation will be


i hope my explanation is clear

Hi Mazan2010,
how about this?
R2 and down:
=SUM(($A$2:$A$22=$P2)*($J$2:$J$22)*(RIGHT($Q2,1)>RIGHT($E$2:$E$22,1)))

please can check attached file as its not give correct value in my real file when apply

both formula give me incorrect value, whats wrong

The data in your latest file gives no indication of how to differentiate current vs previous invoice. In the first file posted you had a column "LF no" and "Number" which were easily differentiated: none of this appears in the current file,

here in this attached file current invoice is in O2
previous Invoice for the same project is in I15
please see attached new file with correct value with green font color

i notice that with this formula its not differentiate between IAF1 and IAF10 , as IAF10 start as its first invoice like IAF1,

how can apply exact match

HTML Code: 

Hi
Try this
=SUM(($A$2:$A$22=$P2)*($J$2:$J$22)*(B2:B22 < VALUE(MID(O2,17,9))))

dear Bo_Ry
please check attached file that not give any result with IFA100
although its give with IFA99 & 101
what you mean F3 lead zero

Dear belinda200
its work well but i notice that when number of character in name changed (increased or decreased ) not give value
for example
in previous attached file the name was
Lab-45123456 number of character using len function is 12
but when name changed to
Lab-4500016806 number of character is 14
for formula i try to changed in part of mid as

HTML Code: 

as you can see i change 17 in original formula to 19
my question how can make it dynamic , suitable for any number
my name always consist of three part as the following
LAB- this part will be fixed
then text or number (variable ) this is mid text or number between two - sign
then -IAF1 to ......... according to number of line of the same name
if there is way to make last part of formula as after text LAB- and last - sign , i think it will be good

i try to modify mid part from original formula to the following
1-

HTML Code: 

or
2-

HTML Code: 

but its not work

If you need the value after IAF then

=SUM(($A$2:$A$22=$P2)*($J$2:$J$22)*(B2:B22 < VALUE(MID(O2,FIND("IAF",O2)+3,9))))

As this appears to be solved can you please mark the thread as such: see "Thread Tools" at top of the first post. Thank you.

Originally Posted by mazan2010

Dear belinda200
its work well but i notice that when number of character in name changed (increased or decreased ) not give value

Hi
Try this:
=SUM(($A$2:$A$22=$P2)*($J$2:$J$22)*(B2:B22 < VALUE(MID(O2,SEARCH("IAF",O2)+3,9))))

Dear JohnTopley
column for amount its get result from other sheet with the following formula

HTML Code: 

when i apply your formula its give error
but when i remove formula from empty row its give result , please how can i solve it
i try to solve it by using filter formula
if there is way to fix your formula without filter

its work also but without formula

HTML Code: 

in empty row
but if this empty row have this above formula its give error

I have no idea what you are referring to so please post a file.

Hi
Delete the formulas in all columns from line 17 and down. This causes the error.

yes i already make it in attached file as in cell Q3 and R2


but its database so i need to keep this formula in range of j2:J50000

The problem is having null("") in column J which result in "Value" errors.

I recommend you learn how to use the "Evaluate Formula" function under "Formulas"

try this

=SUMPRODUCT(($J$2:$J$2000),($A$2:$A$2000=$P2)*($B$2:$B$2000 < VALUE(MID(O2,FIND("IAF",O2)+3,9))))

You can change your formula in column J to :
=IFERROR(VLOOKUP(H2,Database!B1:C22,2,FALSE),0)

instead of using the quotes, use "0", for the IFERROR part in your function.

also adjust column K to :
=FILTER(J2:J34,J2:J34<>0)

use 0 instead of quotes.

The SUMPRODUCT will work with the NULL condition plus does not require an array entry!

Originally Posted by JohnTopley

The SUMPRODUCT will work with the NULL condition plus does not require an array entry!

you are right . just one comment - in Office 365 you dont have array CSE formulas, it becomes dynamic array instead.

thank you all for your help and support

If you are now happy that all problems have been addressed please mark as SOLVED using "Thread Tools" at top of first post.

Thank you.