• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

#value error in sumproduct with Right command


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?


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 :

1F25	25	1
1F25	25	1
1F25	25	1
1F25	25	1
1F25	25	1

where "" indicates blank cells a formula like:

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

Or you can upload a sample workbook with your purpose.


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 :



will also eliminate the #VALUE! errors.



Excel Ninja

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:


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

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




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


1F25 540

1F22 535

1F16 535

1F14 200

Here is the second table obtained from the first one


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



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


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



New Member
Just one more question

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


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


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