Having problem with Sumproduct formula

Jongnj87

New Member
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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
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
Excel Formula:
=SUMPRODUCT(--(B3:B31=N3),H3:H31)
or simpler,
Excel Formula:
=SUMIFS(H3:H31,B3:B31,N3)
Don't forget to make the ranges absolute if you're dragging the formula down the sheet.

Fluff

MrExcel MVP, Moderator
How about
Excel Formula:
=SUMPRODUCT(--(B3:B31=N3),H3:H31)

Jongnj87

New Member
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
Excel Formula:
=SUMPRODUCT(--(B3:B31=N3),H3:H31)
or simpler,
Excel Formula:
=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

New Member
How about
Excel Formula:
=SUMPRODUCT(--(B3:B31=N3),H3:H31)

Thank You so much.

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Threads
1,130,128
Messages
5,640,282
Members
417,133
Latest member
caaronh85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back