using sum if across columns

[Jod84]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've recently inherited a spreadsheet that admittedly has quiet a poor setup with variable going across the page rather than down but I’m stuck with it for now as I’m unable to change. What I’m trying to do is sum across columns based on column header below is a sample<o></o>
<o></o>
Var A01 A02 B01 B02
Day1 1 __5__ 7__ 11
Day2 4 __9__ 15_ 12
Day3 3 _55__ 12_ 33

Apologies for underscores but for some reason when I save thread it removes spaces in above table
Two part question and second part I’m not even sure is possible but firstly I’d like to sum all information below var (top row) so for example if var was A01 it would sum (1+4+3). Secondly if at all possible can I use left function somehow so if var starts with A it will sum both columns i.e. (1+4+3)+(5+9+55). Thanks in advance

 

[T. Valko]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've recently inherited a spreadsheet that admittedly has quiet a poor setup with variable going across the page rather than down but I’m stuck with it for now as I’m unable to change. What I’m trying to do is sum across columns based on column header below is a sample<o></o>
<o></o>
Var A01 A02 B01 B02
Day1 1 __5__ 7__ 11
Day2 4 __9__ 15_ 12
Day3 3 _55__ 12_ 33

Apologies for underscores but for some reason when I save thread it removes spaces in above table
Two part question and second part I’m not even sure is possible but firstly I’d like to sum all information below var (top row) so for example if var was A01 it would sum (1+4+3). Secondly if at all possible can I use left function somehow so if var starts with A it will sum both columns i.e. (1+4+3)+(5+9+55). Thanks in advance

Try this...

Book1
	A	B	C	D	E
2	Var	A01	A02	B01	B02
3	Day1	1	5	7	11
4	Day2	4	9	15	12
5	Day3	3	55	12	33
6	_	_	_	_	_
7	A02	69	_	_	_
8	A	77	_	_	_
Sheet1

To sum for the VAR column.

Formula entered in B7:

=SUM(INDEX(B3:E5,,MATCH(A7,B2:E2,0)))

To sum the VAR columns that start with A:

This array formula** entered in B8:

=SUM(IF(LEFT(B2:E2)=A8,B3:E5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[pagrender]

Here is another option:

Excel Workbook
	A	B	C	D	E
1	Var	AO1	AO2	BO1	BO2
2	Day 1	1	5	7	11
3	Day 2	4	9	15	12
4	Day 3	3	55	12	13
5
6
7	AO2	69
8	B	70
Sheet1

 

[Jod84]

First off thanks to both of you I've tried both and they both work appreciate it as I wasted 2 hours this morning trying to figure this out before I gave up. One more question I have which comes under the nice to have but more then happy with the response I got. This workbook has replications of the sheet i showed above for each month of the financial year Jul, Aug...Jun each has exact same columns and rows (except some months will have more days obviously but blank underneath so no issue in assuming a 31 range). Is it possible to sum across all of these in the one go instead of creating a summary sheet with a column for each month and then adding together.

Thanks

Josh

 

[pagrender]

If you have the same ranges it is possible to sum across several sheets at once. I have not tested this formula with the more complicated formulas, such as the Sumproduct or Index/Match used earlier in this thread.

Cell A1 in Sheet1 has a value of 300
Cell A1 in Sheet2 has a value of 400
Cell A1 in Sheet3 has a value of 500

Here is a formula for this:
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 115px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">300</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Sum all all sheets</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1200</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A4</TD><TD>=SUM(Sheet1:Sheet3!A1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[T. Valko]

First off thanks to both of you I've tried both and they both work appreciate it as I wasted 2 hours this morning trying to figure this out before I gave up.

You're welcome!

One more question I have which comes under the nice to have but more then happy with the response I got. This workbook has replications of the sheet i showed above for each month of the financial year Jul, Aug...Jun each has exact same columns and rows (except some months will have more days obviously but blank underneath so no issue in assuming a 31 range). Is it possible to sum across all of these in the one go instead of creating a summary sheet with a column for each month and then adding together.

Thanks

Josh

Something like this maybe:

=SUM(July:June!A1:A31)