Formula/Macro needed

[Naeblis]

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 =)

[Naeblis]

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 ]

[Mark W.]

{=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.

[Naeblis]

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

[Naeblis]

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 ]

[Mark W.]

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.

[Mark W.]

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 ]

[Naeblis]

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

[Naeblis]

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...

[Aladin Akyurek]

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.