Col A Col B

400.001
5

400.002
6

400.2
3

401.00
5

401.23
5

403
15

405
4


Some time ago, a question was asked how to sum the values in Column B for
rows with the same three digit prefixes (ie. 400 results in 14, 401 results
in 10, etc.)

Suggested formula =SUMPRODUCT((--LEFT(A1:A7,3)=C1)*B1:B7) where cell C1
entry of 400, 401, etc., prduces the desired results.

In order to provide for potential additions to the array, I modified this
formula.

First, I determined that I could return the Value "A7" with the
formula

="A"&COUNTA(A:A)

Then I substituted this formula for "A7" in the original one so that
it read

=SUMPRODUCT((--LEFT(A1:"A"&COUNTA(A:A),3)=C1)*B1:B7)

which results in an error.

Can someone advise?

Dan