+ Reply to Thread
Results 1 to 3 of 3

Sumproduct, dynamic range

  1. #1
    Registered User
    Join Date
    11-06-2007
    Posts
    48

    Sumproduct, dynamic range

    This was originally posted under Worksheet functions forum, i deleted it there and moved it to this forum. No cross-forum posting occured. Thanks

    This is part of a routine.

    I want to do an average for a certain range. Application.Average will not work in this case.
    I have a named range (f) in col c that looks up a value in the range and do some calulations based on conditions on subseqeunt cols.
    The sumif part works fine but when i use columns() in sumproduct() it results in an error.

    Any help appreciated


    Please Login or Register  to view this content.

    Attached is an example of how how the formula should work. but i can't get it to do so.

    Thanks
    Attached Files Attached Files
    Last edited by jmicdk; 04-18-2008 at 06:08 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I'm not quite sure why COLUMN() doesn't work but perhaps try using COLUMNS function like this

    =SUMIF(f,"x",OFFSET(f,0,COLUMNS($E37:E37)))/SUMPRODUCT(N(f="x"),N(OFFSET(f,0,COLUMNS($E37:E37))>0))

    in E37 copied across

    It's more robust too because it still gives the correct result if columns are added or deleted

  3. #3
    Registered User
    Join Date
    11-06-2007
    Posts
    48
    Thank you very much, that helped me out significantly.

+ 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