Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Need help with creating a formula

Copper Contributor

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?

 

RyanC1990_0-1694700452828.png

 

 

Appreciate everyone! 

2 Replies

@RyanC1990 

=SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500

You can apply this rule for conditional formatting.

=$A$2:$D$23

This is the range the conditional format applies to.

invoices.png

 

=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.

@RyanC1990 

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)
)