How do you perform a calculation based on status entry?

[alm395]

Hi!

I am trying to set up an invoice tracker that captures credits, amounts due, payments, and the account balance once any of those fields have been captured. Currently, I have a calculation that I manually update in the remaining balance column depending on the type of transaction. This is the current formula I am using (see sample workbook):

=IFS([@STATUS]="INVOICED",K8-[@[AMOUNT DUE]],[@STATUS]="PAID",K8-[@[AMOUNT PAID]],[@STATUS]="CREDIT MEMO",[@[AMOUNT CREDITED]]+K8)

I was able to get this calculation to work, but I don't know how to make it calculate for the matching Status line items ("Invoiced" line 10 should subtract from the previous "Invoiced" Balance Remaining, not from "Paid" line 9). I hope that makes sense...

Thank you!
ALE

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		1234 - PO TRACKER					PAYMENT OVERVIEW	PO BALANCE	AMOUNT INVOICED	AMOUNT PAID	AMOUNT QUOTED	REMAINING BALANCE
3		PO: 12345678		(10/30/2020 - 10/29/2023)				$ 232,950.00	$ 143,000.00	$ 143,000.00	$ -	$ 89,950.00
4
5		PO # / CHECK #	P.O. DATE / POSTING DATE	INVOICE #	INVOICE DATE	DESCRIPTION	DATE DUE	AMOUNT CREDITED	AMOUNT DUE	AMOUNT PAID	REMAINING BALANCE	PAYMENT DATE	COMMENTS	STATUS		STATUS
6		1234	12/03/2020			OPENING BALANCE		$ 232,950.00	$ -	$ -	$ 232,950.00	-	TRAINING	OPENING BALANCE		INVOICED
7		-	-	1125019	11/05/2020	WORK REQUEST 1	12/05/2020	$ -	$ 10,000.00	$ -	$ 222,950.00	-	(11/05/20 - 11/04/21)	INVOICED		PAID
8		-	-	1125022	11/05/2020	WORK REQUEST 2	12/05/2020	$ -	$ 41,250.00	$ -	$ 181,700.00	-		INVOICED		OVERDUE NOTICE
9		588438	12/03/2020	-	-	PAYMENT FOR INV 1125019 + 1125022	-	$ -	$ -	$ 51,250.00	$ 130,450.00	12/08/2020		PAID		CREDIT MEMO
10		-	-	54401A	11/16/2021	WORK REQUEST 3	12/16/2021	$ -	$ 10,000.00	$ -	$ 120,450.00	-	(11/09/21 - 11/08/22)	INVOICED		QUOTE
11		-	-	54401C	11/16/2021	WORK REQUEST 4	12/16/2021	$ -	$ 41,250.00	$ -	$ 79,200.00	-	(11/09/21 - 11/08/22)	INVOICED		OPENING BALANCE
12		-	-	54493	11/17/2021	WORK REQUEST 5	12/17/2021	a	$ 2,500.00	$ -	$ 76,700.00	-	(11/09/21 - 01/08/22)	INVOICED
13		601260	01/26/2022	-	-	PAYMENT FOR INV 54401A + 54401C + 54493	-	$ -	$ -	$ 53,750.00	$ 22,950.00	01/27/2022		PAID
14		-	-	62610	05/18/2022	WORK REQUEST 6	06/17/2022	$ -	$ 24,000.00	$ -	$ (1,050.00)	-	(5/3-5/5 & 5/10-5/12)	INVOICED
15		604961	05/26/2022	-	-	PAYMENT FOR INV 62610	-	$ -	$ -	$ 24,000.00	$ (25,050.00)	05/26/2022		PAID
16		-	-	67580	08/26/2022	WORK REQUEST 7	09/24/2022	$ -	$ 8,000.00		$ (33,050.00)	-	(08/09/2022 - 08/08/2023)	INVOICED
17		608285	08/25/2022	-	-	PAYMENT FOR INV 67580	-	$ -	$ -	$ 8,000.00	$ (41,050.00)	08/30/2022		PAID
18				68975	09/26/2022	WORK REQUEST 8	10/26/2022	$ -	$ 6,000.00	$ -	$ (47,050.00)		(09/22/2022 - 09/21/2023)	INVOICED
19				-	-	PAYMENT FOR IN 68975	-	$ -	$ -	$ 6,000.00	$ (53,050.00)			PAID
20			09/01/2022			CREDIT FOR OVERPAY		$ 1,000.00	$ -	$ -	$ (52,050.00)			CREDIT MEMO
21								$ -	$ -	$ -	$ -
Sheet1

Cell Formulas
Range	Formula
H3	H3	=$H$6
I3	I3	=SUMIF(Table13[STATUS],"<>QUOTE",Table13[AMOUNT DUE])
J3	J3	=SUM(Table13[AMOUNT PAID])
K3	K3	=SUMIF(Table13[STATUS],"QUOTE",Table13[AMOUNT DUE])
L3	L3	=$H$6-SUM(Table13[AMOUNT PAID])
K6,K21	K6	=[@[AMOUNT CREDITED]]-[@[AMOUNT DUE]]
K7:K20	K7	=IFS([@STATUS]="INVOICED",K6-[@[AMOUNT DUE]],[@STATUS]="PAID",K6-[@[AMOUNT PAID]],[@STATUS]="CREDIT MEMO",[@[AMOUNT CREDITED]]+K6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:N21	Expression	=$N6 = "CREDIT MEMO"	text	NO
B6:N21	Expression	=$N6="INVOICED"	text	NO
B6:N21	Expression	=$N6="PAID"	text	NO
B6:N21	Expression	=$N6="OVERDUE NOTICE"	text	NO
B6:N21	Expression	=$N6="QUOTE"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
N6:N21	List	=$P$6:$P$12

 

[KRice]

If I understand correctly, you will probably want to split the summation into two parts, with each part determining which cell to use. The first IFS statement checks whether STATUS is empty or contains "INVOICED" or something else...and returns the appropriate cell. The second IFS statement is very much like your original one with the "K" cell references removed. This second IFS statements also needs to check whether STATUS is empty (and return a 0) to avoid producing an error in the last row. Try this in K7 and down:

=IFS([@STATUS]="",0,[@STATUS]="INVOICED",IFERROR(LOOKUP(2,1/(N$6:N6="INVOICED"),K$6:K6),K6),[@STATUS]<>"INVOICED",K6)  +  IFS([@STATUS]="INVOICED",-[@[AMOUNT DUE]],[@STATUS]="PAID",-[@[AMOUNT PAID]],[@STATUS]="CREDIT MEMO",[@[AMOUNT CREDITED]],[@STATUS]="",0)

Which version of Excel are you using? Update your profile to display that...as there are other options if you have Excel 365 functions.

 

[KRice]

Carefully check the CREDIT MEMO line...I"m not sure which cell is supposed to be used for the sum (the cell immediately above, or the last INVOICED row?).