Sales for multiple products in 1 Cell

[bilal_aoun]

Would you please help me find the sale in a single cell?
Sales per day in cells B2:H9
Product type A5:A9
Product Price
B16:C21

If we are to transpose the date in cells E17:E24, can we find the total sales in dollar in F18:F24?

 

[jasonb75]

No need to transpose if you use the correct formula.

=SUM(INDEX($B$5:$H$9,0,MATCH(E18,$B$4:$H$4,0)))

 

[bilal_aoun]

Thanks Jason! I had to Transpose to make the dataset Tableau Friendly

 

[bilal_aoun]

Thanks Jason! I had to Transpose to make the dataset Tableau Friendly

I would like to have the sales in $ using the price table in B16:C21

 

[jasonb75]

Sorry, 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.

 

[bilal_aoun]

T

Sorry, 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

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:C21
Thanks,

 

[jasonb75]

I see what you mean now, that would need to be transposed. Possibly this formula will need to be array confirmed with Ctrl Shift Enter.

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

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.

 

[bilal_aoun]

This Great ! Thank you very much for all the help

 

[bilal_aoun]

I see what you mean now, that would need to be transposed. Possibly this formula will need to be array confirmed with Ctrl Shift Enter.

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

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.

So the formula will not do the correct calculation if the price table is sorted differently, and will #N/A if the range is extended

 

[bilal_aoun]

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