Excel Formula

Copper Contributor

Hi everyone. Need a lil help. I have a question regarding my spreadsheet (personal expense spreadsheet). I have list of items in one column and their values in the next column. The items on first column appears more than once and i would like to return the sum of, for example "Briscoes" or "Spotify". Can you help me the process and formulas? See attached snapshot. Thanks a lot guys!query1.JPG Cheers!

3 Replies

Hi Jonathan,

 

There are probably many ways to do this but the first one that came to mind is using SUMIFS and UNIQUE functions. In my table below A1 through B7 is like your table below, with some unique items and some that are listed multiple times. On the right in the "D1" cell ("A"), the formula =UNIQUE(A1:A7) lists all of the items only once. In cell "E1" ("$30.00") the formula is =SUMIFS($B$1:$B$7,$A$1:$A$7,UNIQUE(A1:A7)). I would recommend making the columns on the left a table so that the formulas will update as the table grows and shrinks.

 

A $    10.00 A $    30.00
B $    20.00 B $    20.00
C $    35.00 C $    80.00
C $    45.00 D $    50.00
D $    50.00 E $    10.00
A $    20.00   
E $    10.00   

Hi @johnames 

 

Thanks for the quick reply. Im gonna try this right now and ill let you know. Big thanks mate!

 

Jonathan

Hi @johnames mate!
I used your formula and it worked, exactly what i needed. Thanks again! Cheers!

Jonathan