Summing ranges that contain errors.

[BenBentley]

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?

 

[zacemmel]

=sum(if(iserror(range),0,range))

Control-shift-enter to enter the formula.

Questions?

http://www.excelquestions.com

 

[BenBentley]

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.

 

[zacemmel]

My formula still works?

Questions?

http://www.excelquestions.com

 

[Tom Urtis]

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]

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]

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]

Hi Tom:

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

Regards!

Yogi Anand

 

[BenBentley]

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]

Hi BenBentley:

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

Regards!
Yogi Anand

Edit: Deleted email address
This message was edited by Yogi Anand on 2003-01-19 22:49