Having problem with Sumproduct formula

[Jongnj87]

Hello and good day everyone,

I have a table below for my sales return.

Everything was find until I used the Sumproduct formula on the O2. The formula I used was =SUMPRODUCT((B3:B31=N3)*H3:H31)

I realized that the range from 19 to 31 if nothing it will come out #VALUE!. However, if I range it down from 2 to 18, the formula worked!

I need help in the range if empty it will still calculate for me.

	A	B	C	D	E	F	G	H	I	J	K	L	N	O
1	S/No	Customer Name	Code	Items Purchase	QTY	RETAIL PRICE/PIECE	DISCOUNT	Total Cost	Payment	Delivery	Invoice	Remarks	SELECTED CUSTOMER NAME	ACCUMULATED PURCHASE
2	1	Rui Xian	BX68	黑碧玺柱	1	$68.00		$68.00​					Rui Xian	#VALUE!​
3	2	Ken Tan	HK8	凯蒂猫吊坠	3	$8.00		$24.00​
4	3	Seah Yuh Lih	ZC20	招财猫（金泰）	1	$20.00		$20.00​
5	4	Rui Xian	MM8	米老鼠（粉虎眼）	1	$8.00		$8.00​
6	5	HX Qiu	SY8	四叶草	1	$8.00		$8.00​
7	6	Casey Lim	HL10	海蓝宝（平安扣）	1	$10.00		$10.00​
8	7	丽彬	HL8	黄水晶葫芦	1	$8.00		$8.00​
9	8	Casey Lim	CQT20	超七桶	1	$20.00		$20.00​
10	9	Ping Pong	CQT20	超七桶	1	$20.00		$20.00​
11	10	Ping Pong	MP68	玛瑙聚宝盆	1	$68.00		$68.00​
12	11	Christine Chen	BXX20	碧玺牌 - 星星	1	$20.00		$20.00​
13	12	Casey Lim	BXA20	碧玺牌 - 爱心	1	$20.00		$20.00​
14	13	Christine Chen	HKB8	黑曜石 - 凯蒂猫	2	$8.00		$16.00​
15	14	Christine Chen	ZJX20	紫晶 - 星星	1	$20.00		$20.00​
16	15	Jasmine Tay	BXY20	碧玺牌 - 圆	1	$20.00		$20.00​
17	16	Fiona Lee	SY8	四叶草	1	$8.00		$8.00​
18	17	Fiona Lee	ZZ10	珍珠	1	$10.00		$10.00​
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26	25
27	26
28	27
29	28
30	29
31	30

 

[jasonb75]

If the blanks in column H contain formulas that return a blank then it will cause that error, in those cases you need to use

=SUMPRODUCT(--(B3:B31=N3),H3:H31)

or simpler,

=SUMIFS(H3:H31,B3:B31,N3)

Don't forget to make the ranges absolute if you're dragging the formula down the sheet.

 

[Fluff]

How about

=SUMPRODUCT(--(B3:B31=N3),H3:H31)

 

[Jongnj87]

If the blanks in column H contain formulas that return a blank then it will cause that error, in those cases you need to use

=SUMPRODUCT(--(B3:B31=N3),H3:H31)

or simpler,

=SUMIFS(H3:H31,B3:B31,N3)

Don't forget to make the ranges absolute if you're dragging the formula down the sheet.

It solved my problem. Thank You

 

[Jongnj87]

How about

=SUMPRODUCT(--(B3:B31=N3),H3:H31)

Thank You so much.

 

[Fluff]

Glad we could help & thanks for the feedback.