How to SUMPRODUCT for a variable date, Ignore Blank Cells, Changing Date criteria

[azmat89]

Use SUM PRODUCT to calculate weekly sales for different items. The data has unit sales for each week in columns, their flat rates in a corresponding column. A separate table is used to calculate sales revenue for each item.


Data has blank cells which throws errors.
Sample data below

		Week Starting Date
	Unit Rate	11/02/2019	18/02/2019	25/02/2019	4/03/2019
Item1	$100	5	1	6	1
Item2	$50	6	10	5
Item3	$20		6	10	5
Item4	$40	1	2		6

<tbody>
</tbody>

Report Week	18/02/2019
Item1	=sumproduct(Table1 Date Range = Table2 Report Week)*(???)
Item2
Item3
Item4

<tbody>
</tbody>

Would appreciate the help or any other formulas that can solve this ?

Cheers

 

[Aladin Akyurek]

Would you please post the expected result instead of the formula for Item1?

 

[azmat89]

Would you please post the expected result instead of the formula for Item1?

Returns a #Value

 

[Aladin Akyurek]

Returns a #Value

You should put up there the value which must obtain, not what a non-working formula returns.