SUM of multiple VLOOKUPS

[bbmonkey87]

I am trying to complete an Excel Document which is calculating my employees stats for me. I have 6 tabs. MTD (month to date), week 1, week 2, week 3, week 4, and week 5. On the MTD I am trying to subtotal each stats category (and there are several categories). I have about 80 employees. Each week I have defined as "stats_1", "stats_2", etc.

So... on my MTD I am now trying to do a subtotal summing up each category from each week for each rep. Currently, I have -

=SUM((VLOOKUP(A3,stats_1,2,FALSE)),(VLOOKUP(A3,stats_2,2,FALSE)),(VLOOKUP(A3,stats_3,2,FALSE)),(VLOOKUP(A3,stats_4,2,FALSE)),(VLOOKUP(A3,stats_5,2,FALSE)))

I have also tried -

=(VLOOKUP(A3,stats_1,2,FALSE))+(VLOOKUP(A3,stats_2,2,FALSE))+(VLOOKUP(A3,stats_3,2,FALSE))+(VLOOKUP(A3,stats_4,2,FALSE))+(VLOOKUP(A3,stats_5,2,FALSE))

Currently, on week 2,3,4 and 5 there is no data entered b/c it hasn't gotten here yet - so right now they are blank...

[XOR LX]

Hi and welcome to the forum!

You haven't said what's actually wrong with your current formula - is it just that if there's no data present in one or more of the VLOOKUPS then you need to account for the error(s), i.e.exclude it/them from the sum?

Regards

[bbmonkey87]

My bad sorry - It is coming back saying #VALUE!

[XOR LX]

And are you sure that the results of all five LOOKUPS currently return a numerical value? (Be sure to double-check: some values can look like numbers but if the cell format is set to e.g. text then Excel will not interpret them as such.)

Otherwise, it may be necessary for you to upload a workbook to determine where the issue lies.

Regards

[bbmonkey87]

Well on the week 1, week 2, etc. tabs it's actually feeding off raw data from a different tab that I just paste. So if nothing is pasted for that week it normally would say #N/A, but I already have a formula to change the #N/A into a text of "N/A" I have also tried it as "0" or just blank "" - still nothing

[XOR LX]

But of course - Excel cannot add numbers to text values!

So to go back to my original question: you basically want to exclude #N/A errors (i.e. when one or more of the VLOOKUPs does not return a match) from the sum, correct?

Regards

[bbmonkey87]

Yes, that is correct

[XOR LX]

Ok. You can either add these error traps to your current formula:

=SUM(IF(ISNA(VLOOKUP(A3,stats_1,2,FALSE)),0,VLOOKUP(A3,stats_1,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_2,2,FALSE)),0,VLOOKUP(A3,stats_2,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_3,2,FALSE)),0,VLOOKUP(A3,stats_3,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_4,2,FALSE)),0,VLOOKUP(A3,stats_4,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_5,2,FALSE)),0,VLOOKUP(A3,stats_5,2,FALSE)))

Or, if you're interested, try this array** formula (though without seeing your actual sheet I can't be sure):

=SUM(IF(T(OFFSET(INDIRECT("stats_"&ROW($1:$5)),TRANSPOSE(ROW(INDIRECT("1:100"))),,,))=$A$3,N(OFFSET(INDIRECT("stats_"&ROW($1:$5)),TRANSPOSE(ROW(INDIRECT("1:100"))),1,,))))

The 100 will need to be changed to a number which is equal to or greater than the maximum number of rows in all five of your lookup ranges.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[bbmonkey87]

Perfect thank you!!!!! Now, what about average? Would I just replace the word SUM w/ AVERAGE?

[XOR LX]

You're welcome!

And that depends - if you do that straight replacement, then yes, you will have an average. But it will still be over five data points, even if only three, say, of the VLOOKUPs return a match.

That's because the formulas are set to return a zero in those cases, so you'd still have, e.g.:

=AVERAGE(1,2,3,0,0)

which is (1+2+3+0+0)/5 = 1.2

whereas you might actually just want:

(1+2+3)/3 = 2

Can you clarify?

Regards

[bbmonkey87]

Yes, the second one. I want to omit any 0's or anything that does not have a value.

[XOR LX]

Then (with thanks to Tony Valko ):

=AVERAGE(IF(ISNA(VLOOKUP(A3,stats_1,2,FALSE)),{""},VLOOKUP(A3,stats_1,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_2,2,FALSE)),{""},VLOOKUP(A3,stats_2,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_3,2,FALSE)),{""},VLOOKUP(A3,stats_3,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_4,2,FALSE)),{""},VLOOKUP(A3,stats_4,2,FALSE)),IF(ISNA(VLOOKUP(A3,stats_5,2,FALSE)),{""},VLOOKUP(A3,stats_5,2,FALSE)))

Regards