using a sum() like formula entered asn array to return sum of individual rows or columns

[Legacy 108568]

Howdy,

I am trying to sum individual rows or columns, which normally would be easy, eg sum(A1:A7).

I need a function like sum but entered as an array formula over a series of cells which returns the sum of the columns or rows which I am using it for.

ie

1 3 5 4 8 | ={sum(A1:E3)} <-- (i would like the result to be 21)
2 1 0 3 6 | ={sum(A1:E3)} <-- (i would like the result to be 12)
8 6 8 2 3 | ={sum(A1:E3)} <-- (i would like the result to be 27)
----------------
11 10 13 9 17 <---(also using an array formula to return column sums)

In this case the array formula would be entered in cells A4:E4 to sum columns or F1:F3 to get the sum of each column.

I have tried using all sorts of if statements and subtotal etc but can not work out how to get the results I need.

Any suggestions would be very much appreciated!

 

[mvptomlinson]

Your data is either actually nothing like the example you've provided, or you don't want to use a standard sum formula which will give you the results exactly as you've asked.

In F1 use =SUM(A1:E1) and fill that down to F3. It will automatically update to A2:E2 and A3:E3.

In A4 use =SUM(A1:A3) and fill that right. Same principal as above.

 

[Legacy 108568]

The data is pretty much what I would be entering. Basically, the columns would be titled with staff names, the rows would be labeled with activity types like no of emails responded to, or no. of calls taken. Each activity has its own rate per hour. eg 10 Emails per hour. If a staff member did 5 emails then they have done 0.5 hours of work. I would also input the hours they ere at work for that day.

To work out their efficiency I then divide this by the hours they were available to work. eg, 5 hrs of work/7 hrs of available time.

The calculations of time worked per activity and efficiency is done on a different page in a format which I submit to a database using a macro. I would prefer not to have a column which adds up total hours of work completed for each staff as it is creating more data than what I need to submit into the database. I can just use a pivot table if i wanted that info.

 

[Legacy 108568]

<--This is the structure of my spreadsheets-->

On the input page:
C5:AF5 {=TRANSPOSE(Staff_List)}
C6:AF6 = I input hours works
C8:AF57 = I input the number of times an activity was completed.

B8:B57 {=Activities_List}
A8:A57 {=RE_List} <--- the value of how many is required per hour.

On the calculation page:
F6:BC35 {=TRANSPOSE(Input!C8:AF57/RE_List)}
BD6:BD35 {=TRANSPOSE(Input!C6:AF6)}
BE6:BE35 =SUM(F6:BC6)+(BD6/7.6*0.5) <this changes as not entered as array formula

BF6:BF35 {=IF(BD6:BD35<=0,0,BE6:BE35/BD6:BD35)}

What i want is something like {=if(BD6:BD35<=0,0,getSumOfRow(F6:BC35)/BD6:BD35)}

I know I am making it hard for myself but surely it has to be possible...