Formula/Macro needed

Naeblis

New Member
Joined
Apr 19, 2002
Messages
10
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 =)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
{=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.
 
Upvote 0
So I guess I could substitute the G1:G5 with G:G to look in the whole row then? Thnx for answering. =)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top