bilal_aoun
New Member
- Joined
- May 3, 2021
- Messages
- 25
- Office Version
- 2016
- Platform
- Windows
I would like to have the sales in $ using the price table in B16:C21Thanks Jason! I had to Transpose to make the dataset Tableau Friendly
This formula would give me the sales in units; however, I am trying to get the sales in dollar using the price table in B16:C21Sorry, ignore that remark, I misread 'date' as 'data' and thought you were trying to transpose everything.
If you enter the formula into F18 then fill down it will do as you need. Depending on your regional settings, you may need to change the commas to semicolons
=SUMPRODUCT(INDEX($B$5:$H$9,0,MATCH(E18,$B$4:$H$4,0))*($A$5:$A$9=TRANSPOSE($B$17:$B$21))*$C$17:$C$21)
So the formula will not do the correct calculation if the price table is sorted differently, and will #N/A if the range is extendedI see what you mean now, that would need to be transposed. Possibly this formula will need to be array confirmed with Ctrl Shift Enter.
Because the 2 product lists, A5:A9 and B17:B21 are identical (same products and same order) you could do it without the transposed criteria but it would be at the risk or incorrect results if future changes meant that they no longer matched.Excel Formula:=SUMPRODUCT(INDEX($B$5:$H$9,0,MATCH(E18,$B$4:$H$4,0))*($A$5:$A$9=TRANSPOSE($B$17:$B$21))*$C$17:$C$21)