#value error in sumproduct with Right command

[yabi]

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]

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]

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]

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]

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]

@Yabi,

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


Regards,


Muneer

 

[yabi]

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]

=SUMPRODUCT(--(ISNUMBER(MATCH(Q3:Q170;P182184;0)));S3:S170;T3:T170)


where P182184 holds the sizes 25,22 and 16


Kris

 

[yabi]

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]

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