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

[MEUserII]

Consider the following table, Table A.
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 .

 

[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:

ROW V / COLUMN >	A	B
1	1
2	A
3	1A
4	A
5	2
6	B
7	2B
8	B2
9	3
10	C
11	3C
12	C3

<tbody>
</tbody>

Table B:

ROW V / COLUMN >	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>

 

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet479​

Array Formulas Cell Formula B1 {=SUM(IF(ISNUMBER(LEFT(A1:A12)+0),LEFT(A1:A12)+0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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)