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

L

Legacy 108568

Guest
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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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.
 
Upvote 0
<--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...
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top