--
Regards,
Ron
"Dan Chupinsky" wrote:
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
|