• 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

    Hui...

  • 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

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
""
1F25	25	1
1F25	25	1
1F25	25	1
""
""
1F25	25	1
[/pre]

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.
 

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.
 
@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))
 

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))
 
Top