+ Reply to Thread
Results 1 to 3 of 3

SUM column who's position is unknown

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    41

    SUM column who's position is unknown

    I have a column that I would like to SUM. The position of the column will be variant b/c I have a vb program that is generating the excel file and writing the columns based on other critera. Therefore b/c I do not know where the column will end up, I need to find the column first and then try to SUM it. I am trying the following but it is not working. I get #VALUE. Here is my current formula. I am finding the column using match and then getting its address. I then try to do SUM.


    =SUM((MID(ADDRESS(1,MATCH("mycol*",A1:CZ1,0)),2,2)&2)MID(ADDRESS(1,MATCH("mycol*",A1:CZ1,0)),2,2)&100000))

    If I were to just do =MID(ADDRESS(1,MATCH("mycol*",A1:CZ1,0)),2,2)&2 THEN it would return BZ2.

    and =MID(ADDRESS(1,MATCH("mycol*",A1:CZ1,0)),2,2)&100000 is working and returns BZ100000

    I am trying to combine them using SUM.

    I want the forumla to try and work as follows SUM(BZ2:BZ100000)

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUM column who's position is unknown

    It's easier to use INDEX - try this version

    =SUM(INDEX(A2:CZ100000,0,MATCH("mycol*",A1:CZ1,0)))
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: SUM column who's position is unknown

    Yes, that is much easier to use. However, I am still having a bit of trouble. The text I am searching for in the MATCH, has not yet been written to the cell with the VBA macro I am using. This macro will also be adding the formula to the cell. I am trying to do both in the same step but I do't know if I can do that. Also, the column I am summing will always be the column itself. In other words, I want to write the column heading in Row 1, and again, I do not know exactly where that column will end up. I then want to write the count of that column.

    ="Rows with Duplicate ("&SUM(INDEX(A2:CZ100000,0,MATCH("Rows*",A1:CZ1,0)))&")"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 07-31-2013, 05:57 PM
  2. Replies: 4
    Last Post: 07-13-2010, 09:18 AM
  3. Summing across unknown range in unknown cell!
    By agentred in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 10-28-2009, 10:10 AM
  4. Macro to Add value to unknown position
    By Phixtit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2009, 05:19 AM
  5. [SOLVED] How to find out merge cells from unknown position
    By K.boy in forum Excel General
    Replies: 3
    Last Post: 03-28-2005, 06:06 AM

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