Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formula/Macro needed

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

    Default

    Can anyone tell me how to do this:
    I need a formula/macro to check if there is an entry in say column G, if so, add any other entries in the row it was found and do a sum, and continue to check through the column and sum up all the values it has. but i need it to do it across 12 sheets. Quick example.
    G H I
    1 24 2 1
    2 20 1
    3 3 2
    4 1
    5 3 2 2

    So when this functions does its thing I get the combined sum of rows 1,2 and 5 = 55
    Thnx in advance =)

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

    Default

    sorry that example refused to layout right, but the 1 thru 5 down the left are the rows and the g h i are the colums, then the 3 values that might be in there. 2 didn't have a value in g

    [ This Message was edited by: Naeblis on 2002-04-20 16:41 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    {=SUM(IF(LEN(G1:G5),G1:I5))}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

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

    Default

    So I guess I could substitute the G1:G5 with G:G to look in the whole row then? Thnx for answering. =)

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

    Default

    Actually I need to complictae this a little more, I have 13 columns, one of these is say FOOD (B) TAX1 (G) TAX2 (I)
    So I need to look for entries in column B, if any add the sums of B,G,I in each row it found an entry in B

    _________________
    When life is hard, you have to change...
    Blind Melon - Change

    [ This Message was edited by: Naeblis on 2002-04-20 17:07 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-20 17:03, Naeblis wrote:
    So I guess I could substitute the G1:G5 with G:G to look in the whole row then? Thnx for answering. =)
    Not while using an array formula. The references must be cell ranges (See the Excel help topic for "About cell and range references" for the distinction between a cell range and a column reference).

    BTW, G:G is a column -- not a row reference.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-20 17:06, Naeblis wrote:
    Actually I need to complictae this a little more, I have 13 columns, one of these is say FOOD (B) TAX1 (G) TAX2 (I)
    So I need to look for entries in column B, if any add the sums of B,G,I in each row it found an entry in B

    _________________
    When life is hard, you have to change...
    Blind Melon - Change

    [ This Message was edited by: Naeblis on 2002-04-20 17:07 ]
    {=SUM(IF(LEN(B1:B10),B1:B10+G1:G10+I1:I10))}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    Or, you could use this non-array formulation using column references...

    =SUM(B:B)+SUMIF(B:B,"<>",G:G)+SUMIF(B:B,"<>",I:I)

    [ This Message was edited by: Mark W. on 2002-04-20 19:32 ]

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

    Default

    Thnx a bunch mate, the latter in your last message was able to make it work. Thnx again.



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

    Default

    Now to complicate it one more step, how would I do that across 12 sheets where each sheet is setup the same way, what I am doing is designing an accounting template for my business that tracks my income/expense, but also calculates all my fields for my tax returns etc...

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-20 18:02, Naeblis wrote:
    Now to complicate it one more step, how would I do that across 12 sheets where each sheet is setup the same way, what I am doing is designing an accounting template for my business that tracks my income/expense, but also calculates all my fields for my tax returns etc...
    By prefixing column references with the corresponding sheet names:

    =SUM(Sheet1!B:B)+SUMIF(Sheet1!B:B,"<>",Sheet1!G:G)+SUMIF(Sheet1!B:B,"<>",Sheet1!I:I)

    =SUM(Sheet2!B:B)+SUMIF(Sheet2!B:B,"<>",Sheet2!G:G)+SUMIF(Sheet2!B:B,"<>",Sheet2!I:I)

    etc.

    You can also create dynamic name ranges in for relevant ranges in every sheet and use those names in the respective SUMIF formulas, a move that would eliminate sheet prefixes, like in:

    =SUM(Brange1)+SUMIF(Brange1,"<>",Grange1)+SUMIF(Brange1,"<>",Irange1)

    Brange1 is the name of the relevant range in column B, say, in sheet 1, and so on.



Some videos you may like

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
  •