+ Reply to Thread
Results 1 to 11 of 11

dynamic range specification (based on column header) in sumproduct multiple condition

  1. #1
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Question dynamic range specification (based on column header) in sumproduct multiple condition

    I have this code in VBA,

    value1 = "Product1"
    Value4 = " "

    Range("D8").Formula = Application.Evaluate("SumProduct(--(G2:G10000 = """ & value1 & """) , --(C2:C10000 <> """ & Value4 & """))")

    in above code instead of specifying the range manually (i.e. instead of G2:G10000) I want to specify dynamic range based on header value in row 1.

    I believe for that I want to insert below formula but I don't how to merge these both formula, any suggestion?

    Application.Index(Range("A2:BB10000"), 0, Application.WorksheetFunction.Match("Product", Range("A1:BB1"), 0))

    Thanks in advance.
    -Anand

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    Anand

    You can try this, but it might be easier to get the range with a little code.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    It works, great! thank you again Norie!

    you mentioned it is easier to get range with little code, do you have any suggestion on that? and if I use that then how would it benefit me compare to this formula?

    Thanks!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    anand

    You could get the address like this, which still uses Application.Match.

    Please Login or Register  to view this content.
    You can then incorporate it in the other formula like this.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    Norie,

    I used this and it gave me compile error:

    strColAdd = Range("A2:A10000").Offset(, Application.Match(Range("Product", Range("A1:BB1"), 0)).Address), any suggestion?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    You have an extra parentheses after Address, that's not needed.

  7. #7
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    If I remove that then excel complains about "Expected: list separator or ) "

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    I don't know how it happened but a parentheses was lost between me copying and pasting.

    This has the right no of parentheses and another typo fixed up.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    well one minor problem, now the value of strColAdd is H2:H10000 but it should be G2:G10000, any suggestion?

    Thanks!

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    My bad - not having a good night.

    We need to subtract 1 from the result of the match to the Offset will take us to the right column.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-20-2012
    Location
    Houston, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: dynamic range specification (based on column header) in sumproduct multiple condition

    wonderful, thank you Norie, you been a great help!

    -Anand

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1