# Summing ranges that contain errors.

#### BenBentley

##### New Member
I have a range of data which consists of an employee ID and three other fields for a number of employees. I have this range for every day of the week but not every employee works everyday. The employees are split into teams and I need to sum the data for a whole team, not every team member works everyday. I am using vlookups to find the appropriate field for the correct day, but I cant sum these if a team member is not working that day because one or more of the vlookups returns a #N/A. I know I could use an IF(ISERROR(...) statement, but with up to 22 employees in a team then the formulas get rather large. I have tried a CSE formula but because the array containing team members and the array containing all employees working that day are not the same size I cant get it to work. Does any one have any bright ideas, other than having another table of data for each team?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### BenBentley

##### New Member
Sorry I should have tried to explain that better. I have a display sheet with a drop down that selects the team. This populates a list with the teams login ID's. I then use these login ID's to lookup their time worked from a paste sheet that contains 4 columns of data, eg vlookup('login id',A:D,4). I want a sum of these lookups for the whole team, but if they havent worked they are not on the paste sheet, and therefore return an error.

#### Tom Urtis

##### MrExcel MVP

I'm partly guessing here, because I'm not totally clear on what your problem is, but if it is that you want to sum a range of numbers where in that range is an #N/A error, try this array formula (Ctrl+Shift+Enter):
=SUMIF((A1:A25),"<>#N/A")
Modify range as needed.

Any help? If not, sorry, please repost.

#### Yogi Anand

##### MrExcel MVP
Hi Tom:

=sumif(a1:a25,"<>#N/A") should work fine as a non-array formula -- why have it array entered?

Regards!

Yogi Anand
This message was edited by Yogi Anand on 2002-10-22 23:24

#### Tom Urtis

##### MrExcel MVP

You know what Yogi, I tried both ways right now based on the OP's description of the problem and you are correct...force of habit on my part I guess. If arrays can be avoided I'm all for it. They serve a good purpose where necesssary but this is not a necessary case as you point out. Thanks.

#### Yogi Anand

##### MrExcel MVP
Hi Tom:

I was just curious -- that is why I asked -- Thanks.

Regards!

Yogi Anand

#### BenBentley

##### New Member
Thanks for your help guys, but I think you are trying to sum an array where I have already processed the vlookups. I am trying to use a single cell to do the lookups and sum them without having to have another column that already contains the vlookups. What I really want is to have one cell that looks to a range of, say 25 team members. It then does a lookup of all team members login ID's from another array, say A:B, and if that login ID is present sums the time they have worked. I want to avoid having a column containing the vlookups of the login ID's. Sorry to make it more complicated than need be but I want to make my workbook as tidy as possible. I can email an example if anyone wants to see exactly what I mean.

#### Yogi Anand

##### MrExcel MVP
Hi BenBentley:

You can email a small example to me at yogia@hotmail.com ... and let us take it from there.

Regards!
Yogi Anand

This message was edited by Yogi Anand on 2003-01-19 22:49

Replies
3
Views
166
Replies
2
Views
214
Replies
5
Views
365
Replies
1
Views
192
Replies
1
Views
344

1,148,170
Messages
5,745,170
Members
423,930
Latest member
Simple77

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