Sales for multiple products in 1 Cell (to account for table order and #N/A)

[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?

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

Now I am trying to make this more dynamic (not impacted by NA or the order in B17:C21

 

[Fluff]

I would like to extended the range for the future ( The table will have blanks which will cause an error )

There are no errors in what I posted in post#8 despite the fact I have extended the ranges beyond the data.

 

[bilal_aoun]

Do you have any dates in E18:E24 that do not exist in row 4?

No Its blank for now will be added by user later in the future

 

[bilal_aoun]

1321806_iPhone_Feedback_Template_20210426_005513.xlsx
	A	B	C	D	E	F	G	H
4		3/28/2021	4/4/2021	4/11/2021	4/18/2021	4/25/2021	5/2/2021	5/9/2021
5	Apple	2	6	9	10	2	5	3
6	Orange	1	3	6	2	8	2	4
7	Cherry	1	0	3	5	6	2	1
8	Water	23	0	1	1	0	1	1
9	Juice	10	0	0	2	3	8	0
10
11
12
13
14
15
16		Product	Price
17		Juice	$ 5.00		Date	Total Sale
18		Orange	$ 2.00		3/28/2021	#N/A
19		Cherry	$ 3.00		4/4/2021	#N/A
20		Water	$ 4.00		4/11/2021	#N/A
21		Apple	$ 1.00		4/18/2021	#N/A
22					4/25/2021	#N/A
23					5/2/2021	#N/A
24					5/9/2021	#N/A
Sheet2

Cell Formulas
Range	Formula
F18:F24	F18	=SUMPRODUCT(INDEX($B$5:$H$14,0,MATCH(E18,$B$4:$H$4,0))*($A$5:$A$14=TRANSPOSE($B$17:$B$31))*$C$17:$C$31)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

That is not the formula that I suggested.

 

[bilal_aoun]

Sorry , let me adjust

 

[bilal_aoun]

1321806_iPhone_Feedback_Template_20210426_005513.xlsx
	A	B	C	D	E	F	G	H
1
2
3
4		3/28/2021	4/4/2021	4/11/2021	4/18/2021	4/25/2021	5/2/2021	5/9/2021
5	Apple	2	6	9	10	2	5	3
6	Orange	1	3	6	2	8	2	4
7	Cherry	1	0	3	5	6	2	1
8	Water	23	0	1	1	0	1	1
9	Juice	10	0	0	2	3	8	0
10
11
12
13
14
15
16		Product	Price
17		Juice	$ 5.00		Date	Total Sale
18		Orange	$ 2.00		3/28/2021	147
19		Cherry	$ 3.00		4/4/2021	6
20		Water	$ 4.00		4/11/2021	25
21		Apple	$ 1.00		4/18/2021	33
22					4/25/2021	49
23					5/2/2021	54
24					5/9/2021	15
25
Sheet2

Cell Formulas
Range	Formula
F18:F24	F18	=SUMPRODUCT(INDEX($B$5:$J$10,0,MATCH(E18,$B$4:$J$4,0))*($A$5:$A$10=TRANSPOSE($B$17:$B$31))*TRANSPOSE($C$17:$C$31))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bilal_aoun]

Calculation in F19 is not correct

 

[Fluff]

Chacek that you don't have any leading/trailing spaces on the "products"

 

[bilal_aoun]

Chacek that you don't have any leading/trailing spaces on the "products"

I did , still not doing the math correctly

 

[Fluff]

You sure? From the results you get compared to mine it looks as though it's not counting Apple.