MEUserII
Board Regular
- Joined
- Oct 27, 2017
- Messages
- 91
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
Consider the following table, Table A.
Table A:
<tbody>
</tbody>
I would like to apply a SUMPRODUCT formula for Table A that has a LEFT function argument applied to it, so that it multiples only the first character of each cell, so long as that first character is a number for each of the cells in the range: $A$1:$A$12.
In other words, taking Table A and reducing it to the following Table format, Table B.
Table B:
<tbody>
</tbody>
So, that for Table B's ranges of: $A$1:$A$12 (this range is the first character of each cell from Table A) and $B$1:$B$12 (this range is: 1 if the first character is a number from Table A/0 if that first character is not a number from Table A); the SUMPRODUCT formula of these ranges would be: =SUMPRODUCT( ($A$1:$A$12), ($B$1:$B$12) ); where the result of this formula is: 12.
However, when I try to get this same result from Table A's range of: $A$1:$A$12; by using the SUMPRODUCT formula: =SUMPRODUCT( (LEFT( ($A$1:$A$12), (1) ) ), (--(ISNUMBER(VALUE(LEFT( ($A$1:$A$12), (1) ) ) ) ) ) ); the result of this Table A formula is: 0. How would I correct this SUMPRODUCT formula for Table A's ranges, so that can get the value of 12?
For reference, this question builds on the useful information provided in this thread: https://www.mrexcel.com/forum/excel-questions/1084462-if-value-array-then-substitute-value.html .
Table A:
A | B | |
1 | 1 | |
2 | A | |
3 | 1A | |
4 | A1 | |
5 | 2 | |
6 | B | |
7 | 2B | |
8 | B2 | |
9 | 3 | |
10 | C | |
11 | 3C | |
12 | C3 |
<tbody>
</tbody>
I would like to apply a SUMPRODUCT formula for Table A that has a LEFT function argument applied to it, so that it multiples only the first character of each cell, so long as that first character is a number for each of the cells in the range: $A$1:$A$12.
In other words, taking Table A and reducing it to the following Table format, Table B.
Table B:
A | B | |
1 | 1 | 1 |
2 | A | 0 |
3 | 1 | 1 |
4 | A | 0 |
5 | 2 | 1 |
6 | B | 0 |
7 | 2 | 1 |
8 | B | 0 |
9 | 3 | 1 |
10 | C | 0 |
11 | 3 | 1 |
12 | C | 0 |
<tbody>
</tbody>
So, that for Table B's ranges of: $A$1:$A$12 (this range is the first character of each cell from Table A) and $B$1:$B$12 (this range is: 1 if the first character is a number from Table A/0 if that first character is not a number from Table A); the SUMPRODUCT formula of these ranges would be: =SUMPRODUCT( ($A$1:$A$12), ($B$1:$B$12) ); where the result of this formula is: 12.
However, when I try to get this same result from Table A's range of: $A$1:$A$12; by using the SUMPRODUCT formula: =SUMPRODUCT( (LEFT( ($A$1:$A$12), (1) ) ), (--(ISNUMBER(VALUE(LEFT( ($A$1:$A$12), (1) ) ) ) ) ) ); the result of this Table A formula is: 0. How would I correct this SUMPRODUCT formula for Table A's ranges, so that can get the value of 12?
For reference, this question builds on the useful information provided in this thread: https://www.mrexcel.com/forum/excel-questions/1084462-if-value-array-then-substitute-value.html .
Last edited: