Hi there, I am building a finance worksheet for managing purchase order creation & invoicing.
Sheet 1: full budget overview with a budget against each activity.
Sheet 2: PO tracker where details such as PO number, amount etc. are input
Sheet 3: Invoice tracker where the PO can be selected, invoice amount tracked, etc. You can have multiple invoices for one PO. This sheet also includes the ability to view the remaining amount on PO (pulling info from sheet 2. I have used the following formula for this:
=IFERROR(VLOOKUP(A4,'PO Tracker'!$A$4:$M$498,2,0)-SUMIFS($H$3:H4,$A$3:A4,A4),"")
I would like to add a column that allows me to show remaining amount on the activity (pulling info from sheet 1, calculated by Activity budget - PO amounts).
I have the following formula, based on the following columns: A = PO Number (there are duplicate PO numbers on this sheet), B = PO Amount, E=Activity Name, 'Budget Overview' row B = total budget for that activity. My problem is that my sumif equation is calculating the same amount from row B (PO Amount) multiple times where there are multiple entries with same PO. How can I add another If element into the formular that looks at column A when calculating column B and removes any duplicate calculations based on the same PO seen in column A?
=IFERROR(VLOOKUP(E4,'Budget Overview'!$A$1:$C$45,2,0)-SUMIFS(B$3:B4,E$3:E4,E4),"")
Sheet 1: full budget overview with a budget against each activity.
Sheet 2: PO tracker where details such as PO number, amount etc. are input
Sheet 3: Invoice tracker where the PO can be selected, invoice amount tracked, etc. You can have multiple invoices for one PO. This sheet also includes the ability to view the remaining amount on PO (pulling info from sheet 2. I have used the following formula for this:
=IFERROR(VLOOKUP(A4,'PO Tracker'!$A$4:$M$498,2,0)-SUMIFS($H$3:H4,$A$3:A4,A4),"")
I would like to add a column that allows me to show remaining amount on the activity (pulling info from sheet 1, calculated by Activity budget - PO amounts).
I have the following formula, based on the following columns: A = PO Number (there are duplicate PO numbers on this sheet), B = PO Amount, E=Activity Name, 'Budget Overview' row B = total budget for that activity. My problem is that my sumif equation is calculating the same amount from row B (PO Amount) multiple times where there are multiple entries with same PO. How can I add another If element into the formular that looks at column A when calculating column B and removes any duplicate calculations based on the same PO seen in column A?
=IFERROR(VLOOKUP(E4,'Budget Overview'!$A$1:$C$45,2,0)-SUMIFS(B$3:B4,E$3:E4,E4),"")
