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

  • Thread starter Thread starter Gregory Day
  • Start date Start date
G

Gregory Day

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
 
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.
 
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!
 
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.
 
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.
 
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!
 
Back
Top