Dynamic Range for use in Formula

[PhilW_34]

Hello,

Normally, I would spend more time searching for my solution, but today I'm under a time constraint.

I want to enter this formula in cell C3.

=SUMPRODUCT(($B6:$B2199="Amount")*SUBTOTAL(9,OFFSET(C1,ROW($B6:$B2199)-ROW(C1),)))

However, I need $B6:$B2199 to be a dynamic range. Sometimes it could be B6:B200 or B6:B2300, etc. I want to select B6:Last Row in B.

I have the rest of the code pretty much done. I just can't figure out how to define the range and then put it in my formula. Any help would be greatly appreciated.

Thank you.
Phil

 

[PhilW_34]

Thanks Biff,

I'm just not getting how to apply what you suggested. If I put that formula in C3 just to test it, I get a Value error.

Phil

 

[3link]

I'm stumped. The best I could think of to get rid of the finite property is:

=ADDRESS(MATCH(9.99999999999999E+307,B:B,1), 2)

This would solve for the address of the last cell with a number in column B.

So your formula would become:

=SUMPRODUCT(($B6:ADDRESS(MATCH(9.99999999999999E+307,B:B,1), 2)="Amount")*SUBTOTAL(9,OFFSET(C1,ROW($B6:$B2199)-ROW(C1),)))

But that wouldn't work because the formula would not recognize that as a valid address. If you can find some way to make excel recognize that...

 

[T. Valko]

Thanks Biff,

I'm just not getting how to apply what you suggested. If I put that formula in C3 just to test it, I get a Value error.

Phil

You don't put that formula in a cell...

I see you're using Excel 2007...

Let's assume that data is on Sheet1.

• Goto the Formulas tab>Defined Names>Define Name
• Name: enter some descriptive name for this range. For this example I'll name it MyRange.
• Refers to: here's where you enter the formula. =Sheet1!$B$6:INDEX(Sheet1!$B$6:$B$65536,MATCH("zzzzz",Sheet1!$B$6:$B$65536))
• OK out

Then, your formula becomes:

=SUMPRODUCT((MyRange="Amount")*SUBTOTAL(9,OFFSET(C1,ROW(MyRange)-MIN(ROW(MyRange)),)))

 

[PhilW_34]

Thanks for the suggestions guys.

I figured out how to do what I wanted to do.

Range("B6", Selection.End(xlDown)).Select
        Dim wb As Workbook
        Dim ws2 As Worksheet
            Set wb = ActiveWorkbook
            Set ws2 = ActiveSheet
                wb.Names.Add Name:="tRange", _
                RefersTo:=ws2.Range("$B6", Selection.End(xlDown))

    Range("C3").Formula = "=SUMPRODUCT((tRange=""Amount"")*SUBTOTAL(9,OFFSET(C1,ROW(tRange)-ROW(C1),)))"
    Range("C4").Formula = "=SUMPRODUCT((tRange=""Quantity"")*SUBTOTAL(9,OFFSET(C1,ROW(tRange)-ROW(C1),)))"
    
    Dim lcol As Long
    lcol = Cells(5, 1).End(xlToRight).Column
    
    Range("C3:C4").Copy Destination:=Range(Range("D3"), Cells(4, lcol))

I am not a programmer. I know just enough to cause trouble. I found this solution by piecing together other things I found online. There may be a better way, but it is working for me and hopefully it might help someone else.

Phil