Forum Discussion
Difference between prices
Hi Tea, to get the value from yesterday, you can use SUMIFS() or SUMPRODUCT(). You could also work with XLOOKUP inside FILTER().
In my example (see also attached document) I used SUMPRODUCT. You just have to make sure that the combination of product and date of yesterday only exists once. Otherwise Excel would sum up several values here.
I hope that helps.
I'm on my mobile so i can't attach any pictures. My sheet has in first row: date, product 1,product 2,....product n.
Collumns below are filled with dates (the 1st one) and prices below product names. In another sheet i need excel to automatically calculate the diference between the last two prices whenever i input new information (new prices in first sheet). So if i kad 10/3/2022 and prices for each product and i insert 10/4/2022 and new prices, i need another sheet to subtract them as soon as I'm done in the first sheet
You can display the entries in the columns with COUNTA() and then read out the value with Index. Then simply subtract one from the index, then you have the last value minus the second to last value. But you shouldn't have any empty cells in between.
Does that fit? See also the attached sheet.
=INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B))-INDEX(Sheet2!B:B,COUNTA(Sheet2!B:B)-1)