Summing ranges that contain errors.

BenBentley

New Member
Joined
Oct 21, 2002
Messages
18
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

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

BenBentley

New Member
Joined
Oct 21, 2002
Messages
18
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
Joined
Feb 10, 2002
Messages
11,257

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Feb 10, 2002
Messages
11,257

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
Hi Tom:

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

Regards!

Yogi Anand
 

BenBentley

New Member
Joined
Oct 21, 2002
Messages
18
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
Joined
Mar 12, 2002
Messages
11,454
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
 

Forum statistics

Threads
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.
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
Top