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 H and it is text not numeric).
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
My formula is:
=SUMPRODUCT(--(Q$186:Q$196=Q198);--(S$186:S$196);--(T$186:T$196))
Column T is length of rebars and is a numeric.
When I use above cells of column H in my sumproduct, it returns #Value.
I think it has something to do with with non-numeric values in cells, as there are either "" or numeric values, but I can't find a way to overcome the problem.
The reason that I use IF(H3="";"") is if I don't use it, for some blank cells in H, 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?
It would be great if sumproduct had a feature to ignore blank cells or there was a command to exclude blank cells from the list.
In one of my columns, I have blank cells and other cells are text (like 1F25 in column H and it is text not numeric).
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
My formula is:
=SUMPRODUCT(--(Q$186:Q$196=Q198);--(S$186:S$196);--(T$186:T$196))
Column T is length of rebars and is a numeric.
When I use above cells of column H in my sumproduct, it returns #Value.
I think it has something to do with with non-numeric values in cells, as there are either "" or numeric values, but I can't find a way to overcome the problem.
The reason that I use IF(H3="";"") is if I don't use it, for some blank cells in H, 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?
It would be great if sumproduct had a feature to ignore blank cells or there was a command to exclude blank cells from the list.
Last edited: