Dynamic Range

[Dan Chupinsky]

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

 

[Guest]

--
Regards,
Ron

 

[Guest]

I swear I pasted in the formula then my PC Hiccuped!

Don't just hate computers???? Kidding (sheesh)

Anyway, try this
=SUMPRODUCT(--(LEFT(A1:INDIRECT("A"&COUNTA(A:A)),3)=$C$1)*(B1:INDIRECT("B"&COUNTA(A:A))))

Does that help?

 

[Domenic]

Try...

=SUMPRODUCT((--LEFT($A$1:INDIRECT("A"&COUNTA(A:A)),3)=C1)*($B$1:INDIRECT(
"B"&COUNTA(A:A))))

Alternatively, you can define your dynamic ranges as follows...

Insert > Name > Define

Name: ColumnA

Refers to:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.9999999999999E+307,Sheet1!$B:$B))

Click Add

Name: ColumnB

Refers to:
=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.9999999999999E+307,Sheet1!$B:$B))

Click Ok

Then use the following formula...

=SUMPRODUCT((--LEFT(ColumnA,3)=C1)*ColumnB)

Change the names for the defined ranges to suit.

Hope this helps!

 

[Dan Chupinsky]

Thanks all, it works - - - but for the life of me I don't know why. It seems
like this evaluates to
=SUMPRODUCT(--(LEFT(A1:404),3)=$C$1)*(B1:4))).
But who am I to argue with success!!
Dan