SUMIF with 2 vlookup sheets

Trish Dillon

i am trying to pull stats for a user, by looking up their user ID w/ vlookup and adding it from another sheet one for each year.

What formula works best? i keep getting an error

Paul Newcome

Are you able to provide screenshots with sensitive/confidential information removed, blocked, or replaced with "dummy data" as needed?

What formula are you currently using, and what error is it producing?

Trish Dillon

This is what i have now...

What i need is something that takes both of theses from 2 different pages to get a SUM.

=Sum(IFERROR(VLOOKUP(UID1, {Asana UID-5}, 5, false), "No Activity")+IFERROR(VLOOKUP(UID1, {Asana UID-5}, 5, false), "No Activity")

which i know doesnt work since it has to wrapped into the formula.

Paul Newcome

I'm still not sure I follow. Are you trying to count each year that they are found or sum a group of numbers based on the ID?

Trish Dillon

i have 2 sheets one for 2019 & one for 2020 where i will pull the data from

the main sheet i need the sum of both of those columns but to look them up by User ID to populate the main sheet.

Sheet 2019 has monthly stats and a column with the year total listed by user ID

sheet 2020 is the exact dup of 2019 but with more users listed.

Main sheet need to pull the 2 columns (2019 total & 2020 total) and add them together to get the cumulative within a vlookup.

something like this... =Sum(Vlookup 2019 total + Vlookup 2020)

thank you for assisting, i am trying to wrap my head around how it would look

Paul Newcome

The year total in the 2019 and 2020 sheets... Is it listed as 1 row per user such that each user is only listed once in this column?

There are a few ways that this could go, so getting these details will help with finding the most efficient way.

Trish Dillon

Yes the columns are user id listed once and each month tallys up to total column for 2019

new users are added to the 2020 sheet which may or may not be on the main sheet.

Paul Newcome

Ok. So I am going to us an INDEX/MATCH combo instead of a VLOOKUP. I see in your VLOOKUP you are specifying column 5 which means there is the potential to have your range covering quite a bit of data just to pull a single cell. INDEX/MATCH (when used like this) only references two columns which can help with the performance of the sheet, and it provides a lot more flexibility since the referenced columns do not need to be in a specific order in relation to each other. This provides the flexibility of being able to rearrange your source sheet(s) as needed without having to worry about breaking your formulas.

Here's how it works...

The INDEX function is used to pull data from a range of cells based on a row number and an optional column number specified in the function.

=INDEX(range_to_pull_from, row_number, optional_column_number)

If you are only pulling data from one column (which we are in this case), then we only need to specify the row number. So how do we automate that so that it actually works better than a bunch of manual entry? That's where the MATCH function comes in.

The MATCH function generates a number based on what cell within the specified range contains the data you are searching for.

=MATCH(search_value, search_range, type_of_match)

So if you are looking down a single column in your search_range, the MATCH function will either generate a number that replicates the row number for where the search_value is found, or it will generate a #NO MATCH error if the search_value isn't found (this is where the IFERROR function has already come into play as you have found).

Now lets put this all together to pull the correct number from the [Total 2020] column on the 2020 sheet based on the ID listed in UID@row.

=INDEX({2020 Sheet Total 2020 Column}, MATCH(UID@row, {2020 Sheet UID Column}, 0))

Now we do the same to pull the number from the 2019 sheet.

=INDEX({2019 Sheet Total 2019 Column}, MATCH(UID@row, {2019 Sheet UID Column}, 0))

Now that we have the two numbers, we just add them together...

=INDEX({2020 Sheet Total 2020 Column}, MATCH(UID@row, {2020 Sheet UID Column}, 0)) + INDEX({2019 Sheet Total 2019 Column}, MATCH(UID@row, {2019 Sheet UID Column}, 0))