VLOOKUP, search for values and add them

[kcjaries78]

I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using

week 1
=IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)))

week 2
=IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)))

week 3
=IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)))

week 4
=IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

<TABLE style="WIDTH: 346pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=460 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 44pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: black" width=58 height=17></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=101>Credit Cards</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=104>Customers</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=92>Total Apps</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=105>Per/1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl76 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 1</TD><TD class=xl77 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/8/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>192</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>15.62</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl76 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 2</TD><TD class=xl77 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/15/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>193</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5.18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 3</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/22/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>132</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>week 4</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/29/2008</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>105</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>9.52</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Totals</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>622</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>8.04</TD></TR></TBODY></TABLE>

I have to account for data that is missing becasue they may not have worked, and then the devide by zero error. This works for me if I separate all 4 weeks into thier own cell, which is handy for the employee to see.

But I want to create a master employee sheet for the manager that combines 4 weeks into one cell. So vlookup up would collect all 4 values of total cards for the week and add them up to get 622. I might also want to devide by 4 to get the 4 week average in the same cell; but if the value is zero...

Maybe vlookup is the wrong function, but I haven't seen any other examples of what I am looking to have happen.

I have taught myself through till this point and 3 days of creating, I need some help for the final push. I've tried different combos and looked online... any suggestions?

 

[cornflakegirl]

kcjaries & mvptomlinson - what am I missing? What's wrong with just putting 0s in instead of blanks when there is no result? I understand that you want it blank when reporting the results week by week - but when summing them, I don't see how it hurts? Especially if you're just going to coerce the "" to be a 0 anyway?

 

[kcjaries78]

The other array equations posted didn't seem to help. They kept returning zeros. I tried leaving "" empty and with "0" in this case becasue for this instant I don't need blanks, but alas that didn't work either.

I have my workbook here if anyone wants to look at it. I've highlighted my problem in pink

http://www.4shared.com/dir/6342868/f0427d50/sharing.html

 

[kcjaries78]

Gee do I feel Dumb, E1 wasn't the value I wanted to vlookup for but all the valuves in the A colume. It did work after all, thanks a mill...