How can I get unique sales numbers?-CodePudding

Looking for recomendations for fomulas:

I have a spreadsheet filled with multiple items. I want to get the unique item and the corresponding price. I then want to compare the change in price for the inventory over a certain time. For example:

Current List

Date    Product Price
1/1/2022    Fish    $1.00
1/1/2022    Water   $2.50
1/1/2022    Meat    $3.00
31/1/2022   Water   $2.00
31/1/2022   Fish    $1.50

Desired outcome:

Product Total price Change in Price
Fish    $2.50        $.50
Water   $4.50       -$.50
Meat    $3.00        None

I am still a beginner, any recomendations would be great! Thanks!

CodePudding user response：

use:

=QUERY(QUERY(B2:C; 
 "select B,sum(C),max(C)-min(C) 
  where C is not null 
  group by B"); 
 "offset 1"; )

CodePudding user response：

Something like this for a signed change - looking for something shorter though:

=ArrayFormula({"Product","Total","Change";query(B2:C,"Select B,sum(C) where B is not null group by B label B '',sum(C) ''"),
vlookup(query({row(B2:B),B2:B},"Select max(Col1) where Col2 is not null group by Col2 label max(Col1) ''"),{row(B2:B),C2:C},2,false)
-vlookup(query({row(B2:B),B2:B},"Select min(Col1) where Col2 is not null group by Col2 label min(Col1) ''"),{row(B2:B),C2:C},2,false)}

)