# #value error in sumproduct with Right command

#### yabi

##### New Member
I am trying to use sumproduct function in a table. I have used it a lot and know how it operates.

In one of my columns, I have blank cells and other cells are text (like 1F25 in column A). In order to use it in sumproduct, I make two different columns aS FOLLOWS:

Column Q: =IF(H3="";"";RIGHT(H3;2)+0) this extracts 25 out of 1F25

Column S: =IF(H3="";"";LEFT(H3;1)+0) this extracts 1 out of 1F25

When I use above cells of column A in my sumproduct, it returns #Value.

I don't know how to overcome this problem.

The reason that I use IF(H3="";"") is if I don't use it, for some blank cells in A, the RIGHT(H3;2)+0 command will return ############ and I thought this is the cause of #value error message in sumproduct.

How can I solve this problem?

#### shrivallabha

##### Excel Ninja
Welcome to Chandoo.

You've not posted your SUMPRODUCT formula and how do you use the results from above formulas?

For e.g. for data sample like H3:J10 :

[pre]
Code:
``````1F25	25	1
&#34;&#34;
1F25	25	1
1F25	25	1
1F25	25	1
&#34;&#34;
&#34;&#34;
1F25	25	1``````
[/pre]

where "" indicates blank cells a formula like:

=SUMPRODUCT(I3:I10,J3:J10) works without problem.

#### NARAYANK991

##### Excel Ninja
Hi Shrivallabha ,

But =SUMPRODUCT(I3:I10*J3:J10) will return a #VALUE! error ; this is because the "" is not the same as 0.

Changing the formulae to :

=IF(H3="",0,RIGHT(H3,2)+0)

=IF(H3="",0,LEFT(H3,1)+0)

will also eliminate the #VALUE! errors.

Narayan

#### shrivallabha

##### Excel Ninja
Narayan,

You are right and I'd guess that could be the reason but it is very difficult to comment without seeing OP's usage (as he might be using some more data).

If those are the only columns then following formula entered as ARRAY formula will not require helper columns:

=SUMPRODUCT(IF(H3:H10="",0,LEFT(H3:H10))*IF(H3:H10="",0,RIGHT(H3:H10,2)))

Edit: I guess OP's from EU, so please replace "," with ";" in our formula arguments.

#### nazmul_muneer

##### Member
@Yabi,

You can check your cell formatting. you can convert it to Numeric Value. Its better to upload a sample file.

Regards,

Muneer

#### yabi

##### New Member
Dear NARAYANK991 and other folks

Many thanks to ARAYANK991 as his solution solved the problem.

I was unaware of the difference between "" and zero and assumed both as null. But putting 0 instead of "" solved the problem.

May I buy a mug of beer for you.

Here is my table

Original one

H I

1F25 540

1F22 535

1F16 535

1F14 200

Here is the second table obtained from the first one

Q R S T

25 F 1 5.4

0 F 0 0

22 F 1 5.35

0 F 0 0

16 F 1 5.35

0 F 0 0

Q=IF(H3="";0;RIGHT(H3;2)+0)

S=IF(H3="";0;LEFT(H3;1)+0)

Now I want to get S*T multiplication for all sizes of 25, 22 and 16

=SUMPRODUCT(--(Q3:Q170=25);--(S3:S170);--(T3:T170))

Needles to say that it could be written as follows as well

=SUMPRODUCT((Q3:Q170=P182)*(S3:S170)*(T3:T170))

#### Krishnakumar

##### Member
=SUMPRODUCT(--(ISNUMBER(MATCH(Q3:Q170;P182 184;0)));S3:S170;T3:T170)

where P182 184 holds the sizes 25,22 and 16

Kris

#### yabi

##### New Member
Just one more question

I have to add two sumproduct command in one cell as follows:

SUMPRODUCT(--(B\$3:B\$170=B15);(Q\$3:Q\$170=\$P198)*(S\$3:S\$170)*(T\$3:T\$170))+SUMPRODUCT(--(B\$3:B\$170=B\$28);(Q\$3:Q\$170=\$P198)*(S\$3:S\$170)*(T\$3:T\$170))

Is there a shorter format to combine --(B\$3:B\$170=B15) and --(B\$3:B\$170=B\$28) in one single sumproduct?

#### shrivallabha

##### Excel Ninja
Try adding them at one place like which is equivalent to OR:

SUMPRODUCT((B\$3:B\$170=B15)+(B\$3:B\$170=B\$28);(Q\$3:Q\$170=\$P198)*(S\$3:S\$170)*(T\$3:T\$170))