# 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### mvptomlinson

##### Well-known Member
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.

L

#### Legacy 108568

##### Guest
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.

L

#### Legacy 108568

##### Guest
<--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:

Replies
2
Views
533
Replies
0
Views
301
Replies
5
Views
652
Replies
5
Views
2K
Replies
3
Views
1K

1,191,183
Messages
5,985,174
Members
439,945
Latest member
ospe

### 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.

### Which adblocker are you using?

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

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