Sum per row and Average per column from a second sheet.
 

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you.
Is there anyway I can doe the whole operation in a singe cell?
Just for my own curiosity.

- Thank you!

"akphidelt" wrote:

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

Try this:

=SUMPRODUCT((A1:A4<"")*B1:D4)/COUNTA(A1:A4)


--
Biff
Microsoft Excel MVP


"Gregory Day" wrote in message
...
I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you.
Is there anyway I can doe the whole operation in a singe cell?
Just for my own curiosity.

- Thank you!

"akphidelt" wrote:

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

hahaha, there are things you can do... but some are more complicated then
just doing it the way you have it now. But here is a fairly complicated
formula that would do what you are asking for with the data you presented

=SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0))

If you put the formula in to a cell...

You HAVE to enter it by Holding down CTRL+SHIFT+ENTER

Otherwise you will get an error.
Let me know if that works.

"Gregory Day" wrote:

I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you.
Is there anyway I can doe the whole operation in a singe cell?
Just for my own curiosity.

- Thank you!

"akphidelt" wrote:

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

LOL. You win. My Brain just exploded.

I'll spend the rest of the year breaking that up and reverse enginerring it
to wrap my head around it. ROFL.


"akphidelt" wrote:

hahaha, there are things you can do... but some are more complicated then
just doing it the way you have it now. But here is a fairly complicated
formula that would do what you are asking for with the data you presented

=SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0))

If you put the formula in to a cell...

You HAVE to enter it by Holding down CTRL+SHIFT+ENTER

Otherwise you will get an error.
Let me know if that works.

"Gregory Day" wrote:

I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you.
Is there anyway I can doe the whole operation in a singe cell?
Just for my own curiosity.

- Thank you!

"akphidelt" wrote:

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

Sum per row and Average per column from a second sheet.
 

haha, yea that was quite a mess. If you look at T Valko's equation, that is a
much more user friendly equation that is much more reliable then mine, haha.

Good luck!

"Gregory Day" wrote:

LOL. You win. My Brain just exploded.

I'll spend the rest of the year breaking that up and reverse enginerring it
to wrap my head around it. ROFL.


"akphidelt" wrote:

hahaha, there are things you can do... but some are more complicated then
just doing it the way you have it now. But here is a fairly complicated
formula that would do what you are asking for with the data you presented

=SUM(IF(LEN(A1:A4)0,B1:D4))/SUM(IF(LEN(A1:A4)0,1,0))

If you put the formula in to a cell...

You HAVE to enter it by Holding down CTRL+SHIFT+ENTER

Otherwise you will get an error.
Let me know if that works.

"Gregory Day" wrote:

I appologise, I got my row and column identifiers reversed.
Your plan DID work, thank you.
Is there anyway I can doe the whole operation in a singe cell?
Just for my own curiosity.

- Thank you!

"akphidelt" wrote:

Create column 5 in sheet one with the formula

IF(A1="",0,Sum(A2:A4))

Then copy that formula down
To get the average write the formula in a cell

=Sum(E1:E4)/Countif(E1:E4,"0")

I'm getting kind of mixed up of how you have the numbers as your column
headings and letters as rows. But E should be the fifth column.

"Gregory Day" wrote:

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?