Forum Discussion
Need help with creating a formula
Still learning more complex formulas with excel, so some help would be appreciated with some steps to make this happen.
I'm trying to process a large group of data to come up with a few different things for some reporting. I want to have column D generate the sum total from column C, for all invoices that share the same name in column A, and have that sum appear on the cell of the last row the name appears on. This needs to be continuing for the entire spreadsheet.
Additionally, I need some conditional formatting that where names that exceed the sum total of $500 to highlight in a color so I can then filter by color later on. Please reference the screenshot below, anyone able to assist with something like this?
Appreciate everyone!
=SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500You can apply this rule for conditional formatting.
=$A$2:$D$23This is the range the conditional format applies to.
=IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$23,A2),SUMIF($A$2:A2,A2,$C$2:C2),"")This formula returns the results in column D.
A 365 solution. The key is finding when the account name changes. The attached workbook contains your sample data. Ranges are dynamic.
=LET( acct_change, XMATCH(UNIQUE(Name), Name, , -1) + 1, AcctTotal, LAMBDA(n, IF(XOR(ROW(n) = acct_change), SUMIF(Name, n, InvoiceTotal), "")), MAP(Name, AcctTotal) )
