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

[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

 

[akphidelt]

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]

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!

 

[T. Valko]

Try this:

=SUMPRODUCT((A1:A4<>"")*B14)/COUNTA(A1:A4)

 

[akphidelt]

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,B14))/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]

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]

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!