SUMPRODUCT Formula to Multiply First Character Values of a Cell that are Numbers?

[MEUserII]

Consider the following table, Table A.
Table A:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]B2[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]C3[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

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 .

 

[gaz_chops]

How do you arrive at 12? Is it not 36?

 

[gaz_chops]

I used

=SUMPRODUCT(--ISNUMBER(LEFT(B2:B13,1)+0),A2:A13)

 

[MEUserII]

How do you arrive at 12? Is it not 36?

For clarification, the first row/first column in each of these tables represents the "row header/count header" in Excel; in other words, just the listing of row numbers/column letters. For reference, I have relisted these tables with that clarification.

Table A:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]ROW V / COLUMN >
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1A
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]2B
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]B2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]3C
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]C3
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Table B:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]ROW V / COLUMN >
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MEUserII]

I used

=SUMPRODUCT(--ISNUMBER(LEFT(B2:B13,1)+0),A2:A13)

Ok, but my question is: how do you arrive at 12 on Table A using only Table A's values? I mentioned Table B just as an illustration of my SUMPRODUCT-formula approach on Table A.

I am still stuck on trying to get this same result, 12, 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 SUMPRODUCT formula is: 0.

How would I correct this SUMPRODUCT formula for Table A's ranges, so that can get the value of 12??

 

[gaz_chops]

Try

=SUMPRODUCT(--ISNUMBER(LEFT(A1:A12,1)+0)*IF(ISNUMBER(LEFT(A1:A12,1)+0),LEFT(A1:A12,1)+0,0))

Enter as an array, Ctrl, Shift & Enter

 

[jtakw]

Hi,

Another way, Array formula to be confirmed by CSE, instructions below:

Book1
	A	B
1	1	12
2	A
3	1A
4	A1
5	2
6	B
7	2B
8	B2
9	3
10	C
11	3C
12	C3
Sheet479

Cell Formulas
Range	Formula
B1	{=SUM(IF(ISNUMBER(LEFT(A1:A12)+0),LEFT(A1:A12)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MEUserII]

I used

=SUMPRODUCT(--ISNUMBER(LEFT(B2:B13,1)+0),A2:A13)

Thank you for the solution; is there a formula solution for this that does not require entering with Ctrl+Shift+Enter?

Hi,

Another way, Array formula to be confirmed by CSE, instructions below:

	A	B
A
1A
A1
B
2B
B2
C
3C
C3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet479​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=SUM(IF(ISNUMBER(LEFT(A1:A12)+0),LEFT(A1:A12)+0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the solution; is there a formula solution for this that does not require entering with Ctrl+Shift+Enter?

 

[gaz_chops]

Simplified it more, but still an array!

=SUM(IFERROR(LEFT(A1:A12)+0,0))

 

[jtakw]

If you Don't want to use any Array formula, I'm afraid you'll need a Helper column, which you can Hide out of sight:

Book1
	A	B	C
1	1	1	12
2	A	0
3	1A	1
4	A1	0
5	2	2
6	B	0
7	2B	2
8	B2	0
9	3	3
10	C	0
11	3C	3
12	C3	0
Sheet479

Cell Formulas
Range	Formula
B1	=IFERROR(LEFT(A1)+0,0)
C1	=SUM(B1:B12)