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

[Ron Coderre]

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

[Ron Coderre]

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?
--
Regards,
Ron

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

In article <DKzee.893$Ib4.228@tornado.ohiordc.rr.com>,
"Dan Chupinsky" <dchupinsky@twmi.rr.com> 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

[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
"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:D1FF24C3-83AF-4BAC-AF6F-39653722735A@microsoft.com...
>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?
> --
> Regards,
> Ron
>
>