Totaling Columns of the same type
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Totaling Columns of the same type

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I want to total columns of the same type that are on a different worksheet and they are B3, B13, B23, B33, B43....... because
    B?3 are always going to be one type.

    I would think something like =sum(B*3)

    Is this possible??

    Please help! Thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    do you want to "total the columns" individually, or are you trying to SUM them all together?

    is there a set number of B_3 columns? if so, maybe you could name them as ranges....

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is not a set range of B_3 but I want a complete total of all B_3's any other idea?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    =SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

    But, why not simply

    =SUM(B3,B13,B23) ?

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That my be the answer but it is B_3 to the infinate number to typing/clicking up to B1993 eventually may not be enough?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-16 05:02, kcolls wrote:
    That my be the answer but it is B_3 to the infinate number to typing/clicking up to B1993 eventually may not be enough?
    You will use the SUMPRODUCT formula, I guess.

    Aladin

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 15:13, Aladin Akyurek wrote:
    =SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))
    Hi Aladin:
    Why not simply
    =SUMPRODUCT((RIGHT(B3:B23)="3")*(B3:B23))
    after all it is only a string we are dealing with at that point
    Please advise if there is a specific advantage to converting "3" into a numeric digit with "3"+0
    Your insightful solutions are always a delight!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-16 09:37, Yogi Anand wrote:
    On 2002-04-15 15:13, Aladin Akyurek wrote:
    =SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))
    Hi Aladin:
    Why not simply
    =SUMPRODUCT((RIGHT(B3:B23)="3")*(B3:B23))
    after all it is only a string we are dealing with at that point
    Please advise if there is a specific advantage to converting "3" into a numeric digit with "3"+0
    Your insightful solutions are always a delight!
    Just a precaution in case 3 is entered as a number in a cell of its own, somthing that I'd recommend. Then, it is easier to change

    =SUMPRODUCT((RIGHT(ROW(B3:B23))+0=3)*(B3:B23))

    to

    =SUMPRODUCT((RIGHT(ROW(B3:B23))+0=A1)*(B3:B23))

    where A1 houses 3.

    Hope this sounds convincing .

    Aladin

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com